This is for a budgetary application developed in Access 2007, currently running 2010. The application was working fine. I identified a logic error in a query. While fixing it I realized that I could change the parameters from:
Month(budget.capture_date) = [forms]![f_budget]![tglMonth]
AND
Year(budget.capture_date) = Year([forms]![f_budget]![capture_date])
to simply:
budget.capture_date = DateSerial(Year([forms]![f_budget]![capture_date]),[forms]![f_budget]![tglMonth],1)
Should be fine right? My thought was maybe it would be slightly faster due to less function calls.
The queries worked correctly while testing and typing the parameter in manually when opening the query.
When opening the budget form via DoCmd.OpenForm with criteria, all of the subforms with the DateSerial parameter showed as blank. The field f_budget!capture_date used in the parameter did contain a value.
When opening the form manually every subform gave the following error (no error code):
This expression is typed incorrectly or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
So, after investigation, if you open a parameter query with DateSerial and just skip through the parameters you will receive the error above. If you enter the date manually, it will work correctly. If I open the form via OpenForm with criteria, there is no error, but all the subforms are blank. From there if I change to design view and then back to form view without closing the form, the form will work perfectly. If I close the form and open it manually I will receive the above error for every subform with the DateSerial criteria.
Note that using Year() or Month() and skipping through a query's parameters will just return no results, it will not cause the error above.
This one has me stumped. I've created a new db and imported everything in it, decompiled, repaired, added the DateSerial to the parameter list of the query ect. Doesn't matter, the behavior is consistent. Anyone have any insight?
