# Access DB- taking most recent date from two fields **URL:** \ **Category:** Factual Questions\ **Created:** [August 3, 2004, 9:20pm UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194 "2004-08-03T21:20:52Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Troy\_McClure\_SF](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troy_mcclure_sf/32/4150_2.png) [@Troy\_McClure\_SF](https://boards.straightdope.com/u/Troy_McClure_SF)\ **Post date:** [August 3, 2004, 9:20pm UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194/1 "2004-08-03T21:20:52Z")
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. ```auto 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… ```auto Name Last TB Date McClure, Troy 07/29/2004 Adams, Cecil 08/01/2004 ``` Any help would be greatly appreciated. ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [August 3, 2004, 9:40pm UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194/2 "2004-08-03T21:40:03Z")
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 …) ---
**Author:** ![Troy\_McClure\_SF](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troy_mcclure_sf/32/4150_2.png) [@Troy\_McClure\_SF](https://boards.straightdope.com/u/Troy_McClure_SF)\ **Post date:** [August 3, 2004, 9:47pm UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194/3 "2004-08-03T21:47:24Z")
> [@SCSimmons](#): > > lastTBDate: iif([SkinTestDate]\>=[QuestionnaireDate],[SkinTestDate],[QuestionnaireDate]) Where would I place this formula? ---
**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\ **Post date:** [August 3, 2004, 10:00pm UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194/4 "2004-08-03T22:00:56Z")
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. ---
**Author:** ![Troy\_McClure\_SF](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troy_mcclure_sf/32/4150_2.png) [@Troy\_McClure\_SF](https://boards.straightdope.com/u/Troy_McClure_SF)\ **Post date:** [August 3, 2004, 10:16pm UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194/5 "2004-08-03T22:16:59Z")
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! ---
**Author:** ![Troy\_McClure\_SF](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troy_mcclure_sf/32/4150_2.png) [@Troy\_McClure\_SF](https://boards.straightdope.com/u/Troy_McClure_SF)\ **Post date:** [August 3, 2004, 10:19pm UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194/6 "2004-08-03T22:19:13Z")
> [@Troy McClure SF](#): > > Cool. That works for records where both Skin Test and Questionnaire dates are given, but not if one is blank. 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. ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [August 4, 2004, 1:23am UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194/7 "2004-08-04T01:23:10Z")
In a module copy the following function: ```auto 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]) ---
**Author:** ![grimpixie](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@grimpixie](https://boards.straightdope.com/u/grimpixie)\ **Post date:** [August 4, 2004, 7:58am UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194/8 "2004-08-04T07:58:16Z")
> [@Troy McClure SF](#): > > 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. 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 ---
**Author:** ![Canadjun](https://avatars.discourse-cdn.com/v4/letter/c/76d3ee/32.png) [@Canadjun](https://boards.straightdope.com/u/Canadjun)\ **Post date:** [August 4, 2004, 2:18pm UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194/9 "2004-08-04T14:18:27Z")
> [@Troy McClure SF](#): > > 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! 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. ---
**Author:** ![Troy\_McClure\_SF](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troy_mcclure_sf/32/4150_2.png) [@Troy\_McClure\_SF](https://boards.straightdope.com/u/Troy_McClure_SF)\ **Post date:** [August 4, 2004, 4:07pm UTC](https://boards.straightdope.com/t/access-db-taking-most-recent-date-from-two-fields/258194/10 "2004-08-04T16:07:30Z")
> [@dmartin](#): > > lastTBDate: iif(nz([SkinTestDate])\>=nz([QuestionnaireDate]),[SkinTestDate],[QuestionnaireDate]) That did it! Thank you all for your help. One less thing JCAHO can kick our ass for. 😉