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
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