Access DB- taking most recent date from two fields

Factual Questions
1

MS Access 97, MS Windows NT 4.00.1381

OK- Annual TB compliance- some employees get skin tests, some complete a questionnaire. I have a raw text file in which skin tests and questionnaires are seperate columns.

I work in the clinic, and I need to forward everyone’s latest compliance date to HR, which is not allowed to know if an employee gets the skin test or questionnaire. So I need a query that will look at the Skin Test field and Questionnaire field, check which date is more recent, and put that more recent date into a new TB Compliance Date field.



Name          Skin Test Date Questionnaire date
McClure, Troy 06/12/2002     07/29/2004
Adams, Cecil  08/01/2004     11/22/2003

…needs to yield…



Name          Last TB Date
McClure, Troy 07/29/2004
Adams, Cecil  08/01/2004

Any help would be greatly appreciated.

2

I don’t see any shortcut functions in the Access toolbox for this-you’ll need to use in Iif or Switch. Eg:

lastTBDate: iif([SkinTestDate]>=[QuestionnaireDate],[SkinTestDate],[QuestionnaireDate])

(And just a note for future reference-a function like iif([SkinTestDate]<[QuestionnaireDate],[QuestionnaireDate],[SkinTestDate]) will give wonky results if these fields sometimes get left blank …)

3

Where would I place this formula?

4

Just cut and paste it (I believe that right-clicking on the top line will let you select a zoom option that may make this easier for you) into the top line of the query builder where you would normally select the field that you want to display from a drop-down box. The first name before the colon is the name of the new field (you can change it to whatever you want). The part after the colon is the expression that calculates it. Just make sure that the field names in the formula are exactly the same as they are in the table or query that the new query is built from.

5

Cool. That works for records where both Skin Test and Questionnaire dates are given, but not if one is blank.

If there a way to either correct this directly, or to just insert a dummy “01/01/1904” date in any blank field?

And thank you both for your help!

6

On second look, if Questionnaire date is given or if Question and Skin Test dates are given, it works. If only a Skin Test date is given, it returns a blank.

7

In a module copy the following function:



Public Function vMax(a As Variant, b As Variant) As Variant

    If IsNull(a) And IsNull(b) Then
        vMax = Null
        Exit Function
    End If
    
    If Nz(a) > Nz(b) Then
        vMax = a
    Else
        vMax = b
    End If
    
End Function

You can use this in a query expression, e.g.:
Last TB Date:vMax([Skin Test Date],[Questionnaire Date])

8

The simplest solution would be to replace (in your data source) the blanks with a dummy date, so that the IIf function would have two things to compare…

Grim

9

lastTBDate: iif(nz([SkinTestDate])>=nz([QuestionnaireDate]),[SkinTestDate],[QuestionnaireDate])

nz with one parameter turns missing numbers into 0’s but has no effect on non-missing numbers (and dates are numbers internally). That will prevent a missing date from making screwing up your comparison.

10

That did it!

Thank you all for your help. One less thing JCAHO can kick our ass for. :wink: