Dynamic Query Between 2 Dates (Text Box Refs)

[grobermatica]

Hi,

I'm trying to query a list between 2 dates which have been entered on an underlying form ("frmBetweenDates"... I've done this before and I think its pretty simple but can't remember how to do it... I just keep getting an empty result.

The criteria I have entered is as follows:

Between [Forms]![frmBetweenDates]![txtFrom] And [Forms]![frmBetweenDates]![txtTo]

The text boxes do not have any formats applied to them, the dates are entered in the format dd/mm/yyyy.

I've read somewhere that I have to convert it to US format (mm/dd/yyyy) but this didn't work either... When I convert to US it returns 3 records from the correct date range but a year ago!?!?

When I format to UK format (dd/mm/yyyy) I just get an output of all the records!?

Please help... thanks


Craig

 

[Spenney]

What you have looks OK to me.

Are the regional settings on the PC correct?

Have you tried hard-coding the dates in the Between ... And ... Criteria - Does that work OK?

For what it's worth, I usually manage to get the 'DateSerial' (yyyy,m,d) function to get around date formatting issues if I come across them.

 

[dhookom]

Dates must be in the US format in queries. Check this reference

http://www.mvps.org/access/datetime/date0005.htm

and also this one

http://www.allenbrowne.com/ser-36.html

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Spenney]

In simple criteria you can use UK style dates in the query grid eg #07/06/2006#, but if you look at the SQL view, Access will convert these to US format. Having said that, even though the example date is ambiguous, in my experience the query still returns the records I expect it to.

To eliminate the ambiguity one method would be to have two more text boxes (hidden to the user) on your form, say txtFromSerial and txtToSerial and set their control sources to

=Format([txtFrom],"yyyy,m,d")

and

=Format([txtTo],"yyyy,m,d")

Your query criteria could then be set to

Between [Forms]![frmBetweenDates]![txtFromSerial] And [Forms]![frmBetweenDates]![txtToSerial]

That should work.
Personally, I would format the text boxes txtFrom and txtTo as dd/mm/yyyy and supply an input mask too.