Compare Date Fields

[Malinthas]

Our database contains a set of five date fields. We want to compare them in a query, and ignore all but the most recent. We then want to total the number of clients whose most recent entry is in each field. How do we go about that?

 

[PaulF]

This may be a round-about way of doing it, but it does work. First create a couple of Function, one to trap for null or zero lenght strings and the other to perform the calculation, then create a query that is grouped on the output of the function that did the calculation. This example uses a table named tblDateQ with a field called Client and 5 date fields (date1 - date5).

The 2 functions are:

Function fIsNull(dIn) As Date
If IsNull(dIn) Or dIn = "" Or dIn = " " Then
fIsNull = CDate("01/01/1001&quot
Else
fIsNull = dIn
End If
End Function

Function fNewest(d1 As Date, d2 As Date, d3 As Date, d4 As Date, d5 As Date) As Integer
On Error GoTo errHandler
Dim dtNew As Date
dtNew = d1
dtNew = IIf(d2 > dtNew, d2, dtNew)
dtNew = IIf(d3 > dtNew, d3, dtNew)
dtNew = IIf(d4 > dtNew, d4, dtNew)
dtNew = IIf(d5 > dtNew, d5, dtNew)
Select Case dtNew
Case d1
fNewest = 1
Case d2
fNewest = 2
Case d3
fNewest = 3
Case d4
fNewest = 4
Case d5
fNewest = 5
End Select
Exit Function
errHandler:
MsgBox (Err.Number & " : " & Err.Description)

End Function

The query is

SELECT fNewest(fIsNull([date1]),fIsNull([date2]),fIsNull([date3]),fIsNull([date4]),fIsNull([date5])) AS Newest, Count(tblDateQ.CName) AS RecCount
FROM tblDateQ
GROUP BY fNewest(fIsNull([date1]),fIsNull([date2]),fIsNull([date3]),fIsNull([date4]),fIsNull([date5]));



PaulF

 

[Malinthas]

I appreciate your help, but I don't use the code editor in Access... no habla Visual Basic. I use the standard WYSIWYG-type view in Access. Any help for me there?

 

[psemianonymous]

Yes. Copy his code to the clipboard, starting with the first function and ending with the second. like so:

--Start copy after arrow---->


Function fIsNull(dIn) As Date
If IsNull(dIn) Or dIn = "" Or dIn = " " Then
fIsNull = CDate("01/01/1001&quot
Else
fIsNull = dIn
End If
End Function

Function fNewest(d1 As Date, d2 As Date, d3 As Date, d4 As Date, d5 As Date) As Integer
On Error GoTo errHandler
Dim dtNew As Date
dtNew = d1
dtNew = IIf(d2 > dtNew, d2, dtNew)
dtNew = IIf(d3 > dtNew, d3, dtNew)
dtNew = IIf(d4 > dtNew, d4, dtNew)
dtNew = IIf(d5 > dtNew, d5, dtNew)
Select Case dtNew
Case d1
fNewest = 1
Case d2
fNewest = 2
Case d3
fNewest = 3
Case d4
fNewest = 4
Case d5
fNewest = 5
End Select
Exit Function
errHandler:
MsgBox (Err.Number & " : " & Err.Description)

End Function


<----End copy before arrow------


Now go to modules and click on new. Paste in the code at the end of the module. Close the module. The module name does not matter, so leave it as Module1.


You may now use his functions as he described above: Click on query builder. Click on design view. Cancel on the &quot;choose tables&quot; dialog box. At the top left there is an icon labeled &quot;SQL&quot;. CLick on it. Paste in the SQL he wrote above:


SELECT fNewest(fIsNull([date1]),fIsNull([date2]),fIsNull([date3]),fIsNull([date4]),fIsNull([date5])) AS Newest, Count(tblDateQ.CName) AS RecCount
FROM tblDateQ
GROUP BY fNewest(fIsNull([date1]),fIsNull([date2]),fIsNull([date3]),fIsNull([date4]),fIsNull([date5]));



Now click on the icon to get to design view again.

Save the query and you are done.


PS--next time consider setting up your table structure differently so that you don't have five fields storing the same information. Set them up in a new table, then you can easily get the maximum, minimum, average, count, etc from the list. This question is a prime example of why &quot;table normalization&quot; is useful.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[slreynolds]

http://support.microsoft.com/default.aspx?scid=kb;en-us;182760