CODEWITHSUNDEEP
databasems-access
Salman Farooq
Salman Farooq

Reputation: 159

Max aggregate function with autonumber and date field in access

I have ran into a problem recently. One of the tables I have in MS Access database contains multiple fields but the main focus is on an autonumber field and date field. When I try to apply Select query with Max(autonumber) function and where clause containing date field, the wrong value or rather empty column is shown. as soon as i remove the date field from the where clause, the value is returned fine. My query is attached. Any help would be appreciated.

SELECT MAX(serial) AS Expr1
FROM coaDetails
WHERE (((coaDetails.[title])='CLAIMS')AND ((coaDetails.[dates])=#04/08/2018#));

The above query, serial is autonumber while dates is date/time with format as dd/mm/yyyy

There is no error in the query but it just gives wrong value. as soon as date condition is removed it gives results.

Upvotes: 1

Views: 151

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

In the SQL code, the dates have to be formatted the american way: #mm/dd/yyyy#. In the query design grid, which is a user-friendly, graphical view of the SQL code in the back, the format depends on your regional settings.

Therefore, just use the month first:

SELECT MAX(serial) AS Expr1
FROM coaDetails
WHERE (((coaDetails.[title])='CLAIMS')AND ((coaDetails.[dates])=#08/04/2018#));

Upvotes: 2

Gustav
Gustav

Reputation: 55806

Use the mm/dd/yyyy or yyyy/mm/dd format for the string expression for the date value:

WHERE (((coaDetails.[title])='CLAIMS')AND ((coaDetails.[dates])=#2018/08/04#));

Upvotes: 2

Related Questions