Is it possible for a report to display the oldest date in a query?

[gojets1721]

I have a query where one row is a bunch of dates. Is it possible for a report to display the oldest date listed in the query?

 

[Minty]

Not easily if they are all in different fields.

It sounds as if the data may not be stored correctly, maybe it should be a EventDate, and EventType pair of fields?

 

[gojets1721]

No it's all one field. I just want a report to display the oldest date entry in there

 

[plog]

That makes it worse and Minty even more correct.

Why do you have a bunch of values crammed into one field? Could you post your table structure?

 

[The_Doc_Man]

To do what you requested would require EITHER some complicated VBA code in the report or a restructure of the place holding the dates. Access doesn't work very well "horizontally". It wants vertical arrangement in order to be able sort records.

Further, if you have a single field with multiple dates, you are looking at a situation in which NO native Access comparison or maximization function will help you until you split apart all of those dates. That structure defies major aspects of database design.

 

[gojets1721]

I think I'm explaining it wrong. See attached.

I'm looking to see if on a report, it can display the oldest date in the query

 

[The_Doc_Man]

There is such a thing as a Domain Aggregate. In VBA or on a form or report, you can try

=DMin( "[mydatefield]", "mytable" )

Of course, substitute the correct table and field names. Also note that if you are doing this in a query, it will work but will be really rough on your system due to overhead issues. And because Access only wants recordsources, if you REALLY wanted the oldest date,, you could name a stored query in place of "mytable" because stored SELECT queries are also valid data sources for domain aggregate functions.

 

[theDBguy]

Is it possible for a report to display the oldest date listed in the query?

The file you posted does not include a report. It would have been nice to see what you were actually trying to do.

 

[gojets1721]

The file you posted does not include a report. It would have been nice to see what you were actually trying to do.

Sorry but you're not missing much. It's literally completely blank outside of an empty small box (with the hopes that the earliest date can go in there lol)

 

[gojets1721]

@The_Doc_Man that worked!!!

One final question (and perhaps unfixable)....the dates are in a gaudy format (i.e. 2020-10-06T07:04:12). Any idea how to change this to just a short date when appearing in the report? I selected 'short date' in the format field of the Properties but it didn't change. Probably because the original is so janky. Is there a way you know of to just get rid of the time at least? Unfortunately, these dates stem from data in our CRM system and can't be changed prior to importing into access

Apologies for not including this date/time style in my example. I forgot it was like that

 

[Minty]

Try DateValue(YourField) it is pretty good at deciphering these things.

 

[jumnhy]

Elaborating here: you'd pass the results of the DMin function to the datevalue function, eg: =DateValue(DMin("[YourField]","[YourTable]"))

DateValue explicitly truncates times from date strings. Note that for what it considers to be "invalid" times, it throws an error, and I don't have enough experience working with it to know how much of a problem that would be.


Kludgier solution (particularly if you have a wonky time format that Access won't recognize as a date in DateValue) would be to force it into a string and truncate the time, and then either display the string literal, or for pretty formatting possibilities, pass that back to the DateValue function.

Not positive this would work in the syntax I'm putting as an example below as this isn't my forte, but:

eg =DateValue(Left(CStr(DMin("[YourField]", "[YourTable]")), [#ofCharsInYourDate]))



Also, not to relitigate old news, and maybe this was simply too obvious, but you don't have to use DMin()--you could also just use the Min() aggregate function in your query if that's feasible--eg, SELECT Min(YourDateField) will return the minimum date and the minimum date only.

 

[The_Doc_Man]

If the date ALWAYS includes that "T" in it and no other letter, do this:

CDate( Replace( DMin( "[your-date-field]", "your-table-name" ), "T", " " ) )

(That space in the final quoted string IS significant here).

OR you could run a query using a REPLACE function to just remove the T from the date before trying to use it. But that should never have worked correctly in the sort because if you have date STRINGS with that T in the middle, that isn't a valid date format and Access should have barfed before it got to the times. If you remove the T then that string would be seen as a valid date AND time, and you would be able to sort correctly by time as well as date.

 

[gojets1721]

If the date ALWAYS includes that "T" in it and no other letter, do this:

This worked!!! Thank you so much

 

[The_Doc_Man]

No problem, always happy to help!