Compare Variables

[pib311 via AccessMonster.com]

Hello,

I have about 10,000 records which have four dates that I need to compare. The
dates could be null and it that case I would like to skip that value. I
figured the best way to accomplish this would be to write a function to
handle the dates, but I don't know where to start.

Example of record:

User 1 Date1 Date2 Date3 Date4
(need min date)
AAAA 04/01/2009 03/01/2009 12/31/2008 (null) (needs
to be 12/31/2008)

Any help is greatly appreciated.

 

[John Spencer MVP]

You can use a union query to normalize the data and then get the minimum date

SELECT [User 1], Date1 as TheDate
FROM [TheTable]
UNION
SELECT [User 1], Date2 as TheDate
FROM [TheTable]
UNION
SELECT [User 1], Date3 as TheDate
FROM [TheTable]
UNION
SELECT [User 1], Date4 as TheDate
FROM [TheTable]

Then use that saved query in another query to get the minimum value

SELECT [User 1], Min(TheDate) as MinDate
FROM [TheUnionQuery]
GROUP BY [User 1]

An alternative is to use a VBA function like the following in your query

SELECT [User 1],
fGetRowMin(Date1, Date2, Date3, Date4) as MinDate
FROM [TheTable]


'Handles Text, date, and number fields
Public Function fGetRowMin(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the minimumof a group of values passed to it.
'Sample call:
' fGetRowMin("-21","TEST","2", "3","4","5","6","7",0) returns 0
' fGetRowMin(-21,2,3,4,5,6,7,0) returns -21
'Handles text, date, & number fields.

Dim i As Long, vMin As Variant
vMin = Null

For i = LBound(Values) To UBound(Values)
If IsNull(Values(i)) = False Then
If Values(i) >= vMin Then
Else
vMin = Values(i)
End If
End If
Next

fGetRowMin = vMin

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[pib311 via AccessMonster.com]

Thanks John! I used the first scenario and it worked great.

Thanks again!

You can use a union query to normalize the data and then get the minimum date

SELECT [User 1], Date1 as TheDate
FROM [TheTable]
UNION
SELECT [User 1], Date2 as TheDate
FROM [TheTable]
UNION
SELECT [User 1], Date3 as TheDate
FROM [TheTable]
UNION
SELECT [User 1], Date4 as TheDate
FROM [TheTable]

Then use that saved query in another query to get the minimum value

SELECT [User 1], Min(TheDate) as MinDate
FROM [TheUnionQuery]
GROUP BY [User 1]

An alternative is to use a VBA function like the following in your query

SELECT [User 1],
fGetRowMin(Date1, Date2, Date3, Date4) as MinDate
FROM [TheTable]

'Handles Text, date, and number fields
Public Function fGetRowMin(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the minimumof a group of values passed to it.
'Sample call:
' fGetRowMin("-21","TEST","2", "3","4","5","6","7",0) returns 0
' fGetRowMin(-21,2,3,4,5,6,7,0) returns -21
'Handles text, date, & number fields.

Dim i As Long, vMin As Variant
vMin = Null

For i = LBound(Values) To UBound(Values)
If IsNull(Values(i)) = False Then
If Values(i) >= vMin Then
Else
vMin = Values(i)
End If
End If
Next

fGetRowMin = vMin

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

[quoted text clipped - 11 lines]

Any help is greatly appreciated.