pop up form filter and date fields

  • Thread starter Thread starter Jane Taylor
  • Start date Start date
J

Jane Taylor

I am filtering a report using a pop up form. Everything is
working OK until I try to filter by date or time. An error
message appears (data type mismatch in criteria
expression). I have formatted all the relevant fields I
can find to date/time. I have not used the date field in
an expression on the report - although I have used the the
time fields in an expression, but these are separate
fields.

I would appreciate any help.

Thanks
 
There are a couple of traps here.

Firstly, it's important that Access understands the data type of the text
box on your pop up form. Set the Format property to General Date or Short
Date or similar.

Secondly, if you use a literal date in the WHERE clause of a SQL statement,
you must use American format. This applies to the WhereCondition of an
OpenReport action, the Criteria argument of a DLookup() expression, etc.

For details of the 3 cases where Access can misunderstand your UK date
format, see:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

For an example of how to transfer the dates from a form to the Where
Condition of an OpenReport action, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
 
Back
Top