Calculated field data type (1 Viewer)

[Wysy]

Hi,
I have a query with a calculated field that contains the latest date using a Dmax statement. It works fine. However when i want to order it by ASC or DESC it handles it not like date, but to me it seems like integer. Is there a work around?
thanks

 

[Wysy]

I have found a way that brings me nearer: for the field it used the following format:
Format([calculated_fieldt], "\#mm\/dd\/yyyy\#")
However it fails to order on the year part, but only the month/date. Any idea?

 

[Wysy]

using DatePart to retrieve year month and date in 3 different field makes the trick. But is there a simpler solution maybe?

 

[CJ_London]

It is sorting as a string which is what the format function returns

with regards the original question - show the sql to your query

 

[plog]

This should be done via a sub-query not a DMax(). That way the data you get will be a date.

Let's say this is your field in your main query (MainQuery):

MaxDate: DMax("DateField", "Table2", "[Table1PK] = [Table2FK]")

Then in your subquery (SubQuery) would be this:

SELECT Table2FK, MAX(DateField) AS MaxDate
FROM Table2
GROUP BY Table2FK

Then in you bring in SubQuery into MainQuery, join via Table2FK to Table1PK and just use MaxDate from SubQuery.

 

[Pat Hartman]

The dMax is returning a string. You can convert it to a date. cDate(dMax(...)
OR you can format it as yyyy/mm/dd to sort it correctly.