How do I display the most recent of three dates in an Access quer.

[Claudia1220]

How do I display the most recent (or latter) of three dates in an Access query?

 

[Jerry Whittle]

I'm assuming that the date field is truely a date/time datatype and not a
text field with something that looks like a date.

1. Create a query and sort on that field in descending order. Run it to make
sure it looks correct.

2. Open the query in SQL view. Change where it says SELECT to SELECT TOP 3

One caution: If there is a tie for 3rd place, Access will show all the tied
records.

 

[Jeff Boyce]

"How" depends on "what", as in "what does your data/table structure look
like"?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

Assumption: You have three date fields in one record (row).

The best way would be to redesign your table structure to store the dates as
separate related records in another table. Or use a union query to fix the data.

The easiest way is to use a VBA function - especially if you have no control
over the data being supplied. Below is a function that should work for you.
In your query you would have a calculated column.
Field: LastDate: fGetRowMax([DateOne],[DateTwo],[DateThree])

Copy this function into a VBA module in your database. Save the module with
some name other than fGetRowMax.

Public Function fGetRowMax(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum of a group of values passed to it.
'Sample call:
' fGetRowMax("-21","TEST","2", "3","4","5","6","7",0) returns "TEST"
' fGetRowMax(-21,2,3,4,5,6,7,0) returns 7
' Handles text, date, or number fields -
' only one type of data should be passed in
' Max of 29 values if called from a query.

Dim i As Long, vMax As Variant
vMax = Null

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

fGetRowMax = vMax

End Function

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