simple (hopefully) question about using DMax in a query

[DancingDave]

Can anyone tell me why MS Access produces #Error for MaxDate in all the rows in this query?

SELECT NAMES.[Name ID], DMax("[Last Update Date]","[XREF]","[Name ID] = " & NAMES.[Name ID]) AS MaxDate
FROM [NAMES] INNER JOIN XREF ON NAMES.[Name ID] = XREF.[Name ID];

When I misspell NAMES.[Name ID], it asks me for a parameter value, so that tells me it is recognizing the criteria field syntax ok. But why no good results?

And when I enter a single, valid ID it does work. For example, "[Name ID] = 'WALKDA01'"

Thanks,
Dave

[nico5038]

Can anyone tell me why MS Access produces #Error for MaxDate in all the rows in this query?

SELECT NAMES.[Name ID], DMax("[Last Update Date]","[XREF]","[Name ID] = " & NAMES.[Name ID]) AS MaxDate
FROM [NAMES] INNER JOIN XREF ON NAMES.[Name ID] = XREF.[Name ID];

When I misspell NAMES.[Name ID], it asks me for a parameter value, so that tells me it is recognizing the criteria field syntax ok. But why no good results?

Thanks,
Dave

Did you check that the [Name ID] is numeric ?
When it's a text field use:

SELECT NAMES.[Name ID], DMax("[Last Update Date]","[XREF]","[Name ID] = '" & NAMES.[Name ID] & "'") AS MaxDate
FROM [NAMES] INNER JOIN XREF ON NAMES.[Name ID] = XREF.[Name ID];
 

to add surrounding quotes.

Nic;o)

[DancingDave]

Brilliant! And such a quick response to boot! I've been struggling with this for hours. I never would have come up with that syntax on my own.

Thanks!!!!!!!!!!!!!!!!!

[nico5038]

Glad I could help, success with your application !

Nic;o)