Date Range in Parameter Query

[docar]

I'm trying to run a query based on this:

Between Forms!frm_myForm!Date1 And Forms!frm_myForm!Date2

but can only get the results if Date1 and Date2 are the EXACT begin and end dates in the table.

I tried putting in crazy dates like 1/1/1000 and 1/1/3000 and got a "No rocords found" message, even though there are records in the table.

Thanks in advance for any help.

 

[Aqif]

Humm...

Try this

Format([MyDateField],"dd/mm/yyyy") Between Format(Forms!frm_myForm!Date1,"dd/mm/yyyy") And Format(Forms!frm_myForm!Date2,"dd/mm/yyyy")

Dates are sometimes tricky to get as they are effected by the way regional settings are set on ur pc.

Good luck


Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.

 

[PHV]

Agif, if you want to do lexical ordering with date, use the following format:
WHERE Format([MyDateField],"yyyymmdd") Between Format(Forms!frm_myForm!Date1,"yyyymmdd") And Format(Forms!frm_myForm!Date2,"yyyymmdd")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[docar]

Thank you both your help. The date format was the reason. I also reset the date format and recreated the query and it works even without using the Format in the query as you suggested. It's really strange but it works. Thanks again.