Help with Dlookup()

[Gkirkup]

I have this Dlookup() in a query:

DLookUp("[RECCOUNT]","[CalRecCount0]","[DATE] =' [Forms]![Calmain]![Calsub].[Form]![Day13]'")

I am getting a Data Type mismatch in Criteria Expression error. [Date] is a field in the CalRecCount0 query (which works fine) and [Day13] is a date field on a subform.

Do I have to do anything different because the criteria is a date?

Robert

 

[boblarson]

DLookUp("[RECCOUNT]","[CalRecCount0]","[DATE] =' [Forms]![Calmain]![Calsub].[Form]![Day13]'")

Should be:

DLookUp("[RECCOUNT]","[CalRecCount0]","[DATE] = " & Format([Forms]![Calmain]![Calsub].[Form]![Day13], "\#mm\/dd\/yyyy\#")

But if the form's control has its format property set to be a date, then you may not need to format. But you don't put the form reference in single quotes.

So this may work:

DLookUp("[RECCOUNT]","[CalRecCount0]","[DATE] =[Forms]![Calmain]![Calsub].[Form]![Day13]")

 

[Gkirkup]

Thanks so much. I tried the first one and it worked perfectly!

Robert