DLOOKUP type mismatch with date fields

[mrderekg]

I'm using the following code to populate 2 text boxes with the fiscal year and week. The code runs on the After_Update event of an unbound text box called txtAuditDate which is formatted as Short Date and uses a date picker. tblFiscalWeeks is a cross-reference table that has the fiscal year, fiscal week and the start and end date of each week.

Me!txtFiscalYear = DLookup("Year", "tblFiscalWeeks", "([tblFiscalWeeks]![StartDate]<=[txtAuditDate]" And "[tblFiscalWeeks]![EndDate]>=[txtAuditDate])")
Me!txtFiscalWeek = DLookup("Week", "tblFiscalWeeks", "([tblFiscalWeeks]![StartDate]<=[txtAuditDate]" And "[tblFiscalWeeks]![EndDate]>=[txtAuditDate])")

If I take the DLOOKUP statements and put them in expressions in a new query, they function perfectly but return a Type Mismatch every time they execute in code.

They were working fine yesterday and won't run today. I'm sure this is something really simple and I can't see the forest for the trees.

Many thanks,

Derek

 

[PHV]

I'd use something like this:

Me!txtFiscalYear = DLookup("Year", "tblFiscalWeeks", "#" & Format(Me!txtAuditDate, "yyyy-mm-dd") & "# Between StartDate And EndDate")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrderekg]

I believe I understand the # symbols as denoting dates but why the Format?

Thanks,

Derek

 

[dhookom]

It is a good practice to specify the format of the date since most other locations outside of the U.S. use d/m/y. Queries and domain aggregate functions require unmistakable date formats or m/d/y. Check Allen Browne's page.

Duane
Hook'D on Access
MS Access MVP

 

[mrderekg]

Works perfectly!

And thanks for the reference to Allen Browne's page, I'll check it out so I understand this better.

Thanks so much,

Derek