Between Two Dates

[kadara]

Hi,

I have a problem with filtering a recordset.
I would like to filter the recordset between two dates. When I make a query, and put the "between" criteria, everything working fine.
But when I filter the recordset with the sql statement from a form, I get an error message (no records between two dates).
Please help.

 

[SteveSchapel]

Kadara,

VBA interprets dates as being in US format, unless it is made obvious that another format is applicable. I think your code works, for example, if you change it to:

sql = "SELECT Batch FROM Batch WHERE Batch_Date Between #01-Jul-2010# AND #05-Jul-2010#"

 

[kadara]

OK. It's seems to be working.
Thanks

 

[SteveSchapel]

Kadara,

I am assuming your hard-coding of specific dates into your code was done as part of a testing process, and that in practice you will be more likely to be using variables, or date values from controls on a form, or such like?

 

[kadara]

I put on the form two Date controls (DatePicker). How could I format these controls to show the correct date?
I tried two methods:
1) Format(DTPicker1, "Short Date") - with this format I have no results (I have found no records).
2) Format(DTPicker1, "Medium Date") to get the "01-Jul-2010" format. In this case I get an error message (Syntax error in date).

 

[kadara]

The OS date format not set to US format. How could I change in VBA the date format to US without affecting the OS date format?

 

[SteveSchapel]

Kadara,

Here's how I normally do it:

" ... WHERE Batch_Date Between " & CLng(Me.DTPicker1) & " And " & CLng(Me.DTPicker2)

 

[SteveSchapel]

Here's another way you will often see it done:

" ... WHERE Batch_Date Between #" & Format(Me.DTPicker1, "mm\/dd\/yyyy") & "# And #" & Format(Me.DTPicker2, "mm\/dd\/yyyy") & "#"

 

[kadara]

With the

" ... WHERE Batch_Date Between #" & Format(Me.DTPicker1, "mm\/dd\/yyyy") & "# And #" & Format(Me.DTPicker2, "mm\/dd\/yyyy") & "#"

working fine.

Many thanks.

 

[SteveSchapel]

Kadara,

Yep. The CLng approach would also work fine. Whatever feels best to you.