DMAX expression being returned in wrong format

Question

Futures_Number_Date: DMax("[Date]","CDailyFutures","NumberDate<='" & [numeric_report_Date] & "'")

Entered the above DMAX statement. The Date field is defined with date format in a query (CDailyFutures). When the Date field returned it is not in a date format. I have tried several option to convert to date format but nothing works. The documentation indicates that the expression field will be returned in same format as was in CDailyFutures table which is date format.

Answer 1

There are two issues here (in addition to the inadvisability of using Date as an object name: 

1.  The delimiter for values of date/time data type is the # sign, not a quote character. 

2.  Date literals must be in US short date format of an internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD. 

So, the expression would be like this: 

DMax("[NameOfDateColumn]","CDailyFutures","NumberDate<=#" & Format([numeric\_report\_Date], "yyyy-mm-dd") & "#")   

Using quotes as the delimiter will result in it being interpreted as an arithmetical expression.   This in itself won't cause an error, because the date/time data type in Access is implemented as a 64 bit floating point number, so the expression would result in a date/time value corresponding to the number returned, usually around the end of the 19th century, as 30 December 1899 00:00:00 is represented by zero. You can see this in the immediate window:

? 07/04/2025
8.64197530864198E-04
? Format(8.64197530864198E-04,"dd mmmm yyyy hh:nn:ss")
30 December 1899 00:01:15

The exception to the above is when setting the DefaultValue property of a column or control.  This property is always a string expression, regardless of the data type of the column in question, so should be delimited with quotes characters, e.g. 

Me.MyControl.DefaultValue = """" & Me.txtMyDate & """"   

where txtMyDate contains a date/time value.  The format is not important here, as the format of the value in the control will be a format compatible with the date format of the system.  So, unlike a date literal, the value 07/04/2024  would be correctly interpreted as 4th July in the USA, as would 04/07/2024 here in the UK.  A date literal incorrectly expressed in UK format as #04/07/2024#, on the other hand, would change 4th July to 7th April in the UK!

Answer 2

"I have tried several option to convert to date format but nothing works."

Would you mind showing us those things you tried? And the results that don't work the way you need them?

Answer 3

First Date is a reserved word in Access and shouldn't be used as a field name.

Where are you using or viewing the display field? Don't worry about how the value display when viewing the query. What is important is how it displays on the form or report where you are displaying the data. Towards that end, have you tried formatting the Control displaying the data?