Date Format Problem Using Filter

[papic1972]

Hi all,

I have 2 unbound text boxes 'txtStartDate' & 'txtEndDate' on my form 'frmLocal' that i use to filter dates on my subform 'subfrmLocal'.
The code on my command button is:

Me.subfrmLocal.Form.RecordSource = "SELECT * FROM qryLocal WHERE qryLocal.EntryDate BETWEEN #" & Forms![frmLocal]![txtStartDate] & "# And #" & Forms![frmLocal]![txtEndDate] & "#"

My date fields in my query 'qryLocal' are formatted correctly as 'dd/mm/yyyy', however when i type in a date using this format into my unbound text boxes i cannot retrieve data. I have to type in it using the US format of mm/dd/yyyy to retrieve data that is then shown on my subform in dd/mm/yyyy. This is driving me banana's!!!

Can anyone help with my code so i can retrieve my data by inputting into my unbound text boxes the dd/mm/yyyy format?

Many thanks.

 

[boblarson]

Change this to:

"SELECT * FROM qryLocal WHERE qryLocal.EntryDate BETWEEN #" & Format(Forms![frmLocal]![txtStartDate], "mm/dd/yyyy") & "# And #" & Format(Forms![frmLocal]![txtEndDate], "mm/dd/yyyy") & "#"

That will let you type in the date the way you want, yet send the query what it wants.

 

[papic1972]

Hi Bob,

You're a legend!!! That worked perfectly!!!
Thank you so much!

 

[namliam]

Be carefull tho!

DD/MM/YYYY doesnt allways translate properly into MM/DD/YYYY, make sure to test that everything is as it should be !