Dlookup Criteria Syntax

0
votes

I have spent hours searching the net and can not find and answer to why my syntax is wrong. I have a table with many records, they are all date stamped using Now(). I am using the following code to get the most recent record.

MaxDate = DMax("AuditDateTime", "Tbl_Data_Claim")

This works perfect. Now I am trying to use DLookup to get the field I want and put it in my form.

Me.Frm_Data_Claim_subform!TBSClaim_DOL = DLookup("[Claim_DOL]", "Tbl_Data_Claim", "[AuditDateTime] = " & [MaxDate] & "'")

I get a Run-time error '3075' Syntax error (missing operator) in query expression '[AuditDateTime] = 9/16/2018 2:32:14PM". The criteria is working because my timestamp is showing in the error. I am sure it is just a misplaced quote or something simple. I have tried all kind of permutations and nothing seems to work. Thank you in advance.

2
sqlms-accessvbams-access-2010

2 Answers

2
votes

Your DMax returns a date value, while the filter in DLookup requires a string expression of that date value.

The universal (also working outside the US) method would be:

"[AuditDateTime] = #" & Format([MaxDate], "yyyy\/mm\/dd hh\:nn\:ss# & "#"

However, you could do it in one go and avoid the formatting:

Me.Frm_Data_Claim_subform!TBSClaim_DOL = DLookup("[Claim_DOL]", "Tbl_Data_Claim", "[AuditDateTime] = DMax('AuditDateTime', 'Tbl_Data_Claim')")

Note the change of double quotes to single quotes.

0
votes

After posing the question. I kept looking and found the answer, well kind of. I found something close and then modified until it worked.

Me.Frm_Data_Claim_subform!TBSClaim_DOL = DLookup("[Claim_DOL]", "Tbl_Data_Claim", "AuditDateTime = #" & MaxDate & "#")

Because the variable is a date I need bracket it in #'s. Hope this helps someone else.