Dmax not returning latest value for text field

[tagteam]

I am using dmax to get the latest value from a table using two criteria. It works fine for the dates but does not work on the text field. The fields I have are id, customername, name, begindate, enddate (not the real field names). So when I use the dmax for the begindate and enddate it works fine, however, it does not work for name.

What I want to be able to do is use DMax for the id field but return the customername, not the id.



Is this possible

[Micron]

Should be. As always, post what you tried. For anything more than a few lines of code, best to use code tags (# on thread toolbar).
Your issue is likely incorrect concatenation, or not encasing text values in quotes (single are easiest) or both.

EDIT - BTW, Max is not necessarily what you consider to be last or max, especially if you're trying to get either from a text field.

[tagteam]

I am using

=DMax("customername","CustomerAssignments","CarNumber = " & [Forms]![f_EMEUpdates]![CarNumber] & " And CarInitials ='" & [Forms]![f_EMEUpdates]![CarInitials] & "'")

I also use

=DMax("customerbegin","CustomerAssignments","CarNumber = " & [Forms]![f_EMEUpdates]![CarNumber] & " And CarInitials ='" & [Forms]![f_EMEUpdates]![CarInitials] & "'")

And that one works fine I think because it is a date. However, as you mentioned using a Dmax for text will yield different results, I am not sure what it considers the max. That is why I want to look up the most recent ID but return the text value in the customer field of that record. Dmax and Dlookup both only look up the max of the field you put in the first criteria.

What I need to be able to do is look up the Max ID for records with the current carnumber and car initials but return the customername that is in that record.

[Micron]

What I want to be able to do is use DMax for the id field...

No idea what this is; an autonumber field?
maybe

DLookup("customername","CustomerAssignments","CarNumber = " & DMax('yourField','yourDomain'))
or
DLookup("customername","CustomerAssignments","CarNumber = " & Max(yourNumberField))

EDIT - to answer your other question, max of a text field is simply alphabetically - assuming the comparison is not binary. If so, case matters; e.g. A < a.

[Minty]

Does this work?

=Dlookup("CustomerName","CustomerAssignments","ID = " & DMax("ID","CustomerAssignments","CarNumber = " & [Forms]![f_EMEUpdates]![CarNumber] & " And CarInitials ='" & [Forms]![f_EMEUpdates]![CarInitials] & "'"))