Something weird happening in a database which I’d welcome some guru advice on.
Situation is I have some queries which extract record counts for an export, based on a user entered date range.
The queries get the values from the form fields,
[Forms]![Main Menu]![DateFrom] and …[DateUntil]
Assuming the Main Menu form is open, I can open any of the queries and get the count of selected records Ok
Where things go weird is if I open the queries in code. Some give the right count, others were only giving the count of the total number of records in that table.
Tracked it down to the fact that in some cases the ‘date’ is held in a Text field (I know it shouldn’t but for other reasons it has to be)
I wrote a function to convert to coerce it to a date, using CDate, and this works in the queries when opened
In the code I set a QueryDef to the query, then loop through the Parameters collection using Eval to set the Value for each parameter, then create the Recordse, but for the ‘text’ dates this doesn’t work, checked the values and they are Dates
– solved it another way, but would like to know what’s going wrong in the original
