Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DLOOKUP type mismatch with date fields

Status
Not open for further replies.

mrderekg

IS-IT--Management
Feb 20, 2006
6
US
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
 
I'd use something like this:
Code:
Me!txtFiscalYear = DLookup("Year", "tblFiscalWeeks", "#" & Format(Me!txtAuditDate, "yyyy-mm-dd") & "# Between StartDate And EndDate")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I believe I understand the # symbols as denoting dates but why the Format?

Thanks,

Derek
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top