Is it possible for a report to display the oldest date in a query? (1 Viewer)

templeowls

Registered User.
Local time
Today, 06:36
Joined
Jun 11, 2019
Messages
112
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

AWF VIP
Local time
Today, 14:36
Joined
Jul 26, 2013
Messages
8,760
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?
 

templeowls

Registered User.
Local time
Today, 06:36
Joined
Jun 11, 2019
Messages
112
No it's all one field. I just want a report to display the oldest date entry in there
 

plog

Banishment Pending
Local time
Today, 08:36
Joined
May 11, 2011
Messages
10,562
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

Immoderate Moderator
Staff member
Local time
Today, 08:36
Joined
Feb 28, 2001
Messages
20,625
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.
 

templeowls

Registered User.
Local time
Today, 06:36
Joined
Jun 11, 2019
Messages
112
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
 

Attachments

  • Database11.accdb
    388 KB · Views: 40

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:36
Joined
Feb 28, 2001
Messages
20,625
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

I’m here to help
Staff member
Local time
Today, 06:36
Joined
Oct 29, 2018
Messages
16,078
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.
 

templeowls

Registered User.
Local time
Today, 06:36
Joined
Jun 11, 2019
Messages
112
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)
 

templeowls

Registered User.
Local time
Today, 06:36
Joined
Jun 11, 2019
Messages
112
@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

AWF VIP
Local time
Today, 14:36
Joined
Jul 26, 2013
Messages
8,760
Try DateValue(YourField) it is pretty good at deciphering these things.
 

jumnhy

Member
Local time
Today, 09:36
Joined
Feb 25, 2021
Messages
68
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

Immoderate Moderator
Staff member
Local time
Today, 08:36
Joined
Feb 28, 2001
Messages
20,625
If the date ALWAYS includes that "T" in it and no other letter, do this:

Code:
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.
 

templeowls

Registered User.
Local time
Today, 06:36
Joined
Jun 11, 2019
Messages
112
Code:
If the date ALWAYS includes that "T" in it and no other letter, do this:

This worked!!! Thank you so much
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:36
Joined
Feb 28, 2001
Messages
20,625
No problem, always happy to help!
 

Users who are viewing this thread

Top Bottom