Date criteria – won’t recognise

[barrysprout]

I can’t get a select query to recognise date criteria.

I want to select records between Jan 1st and the current date of any given year. The year is referenced from a control on an unbound form where the user selects various criteria. I have used the dateserial function as below to get the date format for the criterion but when I run the query I get no results. When I evaluate each dateserial function in the immediate window, I get the correct dates I'm after. If I reverse the date order to make it yyyy/mm/dd, the query gives me all data in the table.

Between DateSerial([Forms]![ChSumArea]![Yr],1,1) And DateSerial([Forms]![ChSumArea]![Yr],Month(Now()),Day(Now()))

My system date format is dd/mm/yyyy

Any suggestions?

 

[genomon]

Yup - try telling Access the string is a date by enclosing it in pound signs (and forget about DateSerial):

"...BETWEEN #" & [Forms]![ChSumArea] & "# AND #" & _
Now() & "#"

The Microsoft mascot is a butterfly.
A butterfly is a bug.
Think about it....

 

[barrysprout]

Thanks but it doesn't work. I'm using a saved query and the QBE grid gives me an "invalid date format" error with the above. Also I can't forget about Dateserial or other date functions because I need it to compute the first day of the year for the relevant year though I suppose I could use an input mask on the unbound form to get 01/01 but it's not ideal.