To show the records between two dates

[sajankk]

hi,

i have a table with one field(edate) is date(format -short date)type in access 2003 database- VB 6.0. i want to search in the table and display the records in between two dates. those dates are picked up from the DTPicker1 and DTPicker2 control. My sql stt., is

strsql="select * from mytable where edate between #" & DTPicker1.value & "# and #" & DTPicker2.value & "# ".

am not getting the exact records as i expecting. is anything wrong in this query. and even i tried >=dtpicker1.value and <=dtpicker2.value. This too not working

saj

[zeezee]

Did you try formating the date ?like

Format(DTPicker1.value , "dd/mm/yyyy")


Check the date format in your DB and try to format your DTP value to that format.

[anhn]

No. Dates in Access SQL only work correctly with US Date format "mm/dd/yyyy"

strsql="select * from mytable where edate between #" & _
       format(DTPicker1.value,"mm/dd/yyyy") & "# and #" & _
       format(DTPicker2.value,"mm/dd/yyyy") & "# "

or

strsql="select * from mytable where edate >= #" & _
       format(DTPicker1.value,"mm/dd/yyyy") & "# and edate <= #" & _
       format(DTPicker2.value,"mm/dd/yyyy") & "# "

[zeezee]

Thats what I also meant. Format it to whatever the format in the DB

[Hack]

am not getting the exact records as i expecting. is anything wrong in this query. and even i tried >=dtpicker1.value and <=dtpicker2.value. This too not working

Did you run the query directly from inside Access and through your VB program so you could compare the results?

[si_the_geek]

The problem is the format of the dates, as anhn said. When you convert a Date to a String, it is formatted using your Regional Settings (unless you use Format function and specify otherwise).

For SQL statments, dates need to be in the US format (mm/dd/yyyy) or ISO format (yyyy-mm-dd). This applies no matter what the settings are in your Regional Settings, or the database system, etc.

For more information, see the article How can I work with dates correctly? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[sajankk]

hi all,

thanx for all the replies.. i try as zeezee said . but it is not working properly and and am not converting the date to string. am using the format dd/mm/yyyy. i have to read the si_th_geek's link. meantime any suggestion can be appreciated


saj

[sajankk]

Did you run the query directly from inside Access and through your VB program so you could compare the results?

no HACK i will do it

[zeezee]

Well Saj, what is the date format in your DB is it mm/dd/yyyy , dd/mm/yyyy , yyyy-mm- dd, etc ... ?

And for the date format in SQL , well , I just checked in my pc , where date format is dd/mm/yyyy, and when I create a field which is date/time in access, the values are in dd/mm/yyyy format.
And when I do a smple query with a where cluase, It works for both mm/dd/yyyy and dd/mm/yyyy ?

And when I change the date format in the design mode, the values are automatically converted to correct format. So does this mean that the Fromats US (mm/dd/yyyy) and ISO (yyyy-mm-dd) are not compulsory ?

[anhn]

This is what comes from Microsoft Visual Basic Help:

Use International Date Formats in SQL Statements
You must use English (United States) date formats in SQL statements in Visual Basic.
However, you can use international date formats in the query design grid.

This also applied for SQL Statement in Access.
In UK or Australia, with system date format dd/mm/yyyy, within Access Query Design View,
if you type in #23/9/2007# : Access will accept it as it is
if you type in #9/23/2007# : Access will convert it to #23/09/2007#
if you type in #2007-09-23# : Access will convert it to #23/09/2007#
However, if you look at SQL View, you will see that Access converts #23/09/2007# to #9/23/2007#,
so : only US_Date format #mm/dd/yyyy# is valid in SQL Statement.
although in Query Design View you must enter with your system date format.

I am in Australia and I have been aware of this issue since more than 10 years ago as I have worked with Access and VBA and VB since then.

If using format "dd/mm/yyyy" or leave it for the system decides then it seems to be no problem with day > 12 as VB is smart and it knows a number greater than 12 cannot be a month so it will correctly convert it to US format. But in case day <= 12, you may have problem as VB may mix up day and month.

If you don't trust me, do it with your own way and take the risk.

[sajankk]

But in case day <= 12, you may have problem as VB may mix up day and month.

If you don't trust me, do it with your own way and take the risk.

i am trusting you people very much. but what to if the day <12. i feel the same problem is effecting me in my searching too. how to go about?

[anhn]

Change your code to what I said in Post#3 then you won't have any problem with any date.
That means always use US_Date format #mm/dd/yyyy# in SQL statement.

It's simple to test in Immediate window:

? #8/10/2007# > #23/9/2007#
False
? #8/10/2007# > #9/7/2007#
False
? #8/10/2007# > #7/9/2007#
True
? #10/8/2007# > #23/9/2007#
True
? #10/8/2007# > #9/23/2007#
True

What VB will understand the formats above?
All of them are "mm/dd/yyyy" except 23/9/2007 is "dd/mm/yyyy"
and VB will internally convert 23/9/2007 to 9/23/2007 because 23>12, 23 cannot be a month.

[si_the_geek]

and and am not converting the date to string.

Oh yes you are!

To understand why, I would recommend reading this Classic VB FAQ article: Why are my dates not working properly?

Well Saj, what is the date format in your DB is it mm/dd/yyyy , dd/mm/yyyy , yyyy-mm- dd, etc ... ?

...So does this mean that the Fromats US (mm/dd/yyyy) and ISO (yyyy-mm-dd) are not compulsory ?

As I said before - the date format settings in the database/regional settings/etc do not matter at all. What matters is the settings of the SQL language, which only accepts US or ISO formats (except for some database systems which have completely different methods, as explained the the FAQs).

As anhn explained, some systems (such as Access, and VB itself) will automatically convert the format (by switching the Month & Day parts) if the Day is larger than 12.


That means if you write code using todays date (in the format you want to use), it will appear to be fine, but if the date changes to a lower day (like at the start of next month) it will no longer work. If you use the correct format(s), it will work correctly now and in the future.

What you need to use is what anhn posted near the start of this thread, which is the same as what is shown in the first FAQ article that I linked to.

[zeezee]

Oh yes you are!

To understand why, I would recommend reading this Classic VB FAQ article: Why are my dates not working properly?
As I said before - the date format settings in the database/regional settings/etc do not matter at all. What matters is the settings of the SQL language, which only accepts US or ISO formats (except for some database systems which have completely different methods, as explained the the FAQs).

As anhn explained, some systems (such as Access, and VB itself) will automatically convert the format (by switching the Month & Day parts) if the Day is larger than 12.


That means if you write code using todays date (in the format you want to use), it will appear to be fine, but if the date changes to a lower day (like at the start of next month) it will no longer work. If you use the correct format(s), it will work correctly now and in the future.

What you need to use is what anhn posted near the start of this thread, which is the same as what is shown in the first FAQ article that I linked to.

OK OK. You are right. And anhn is right too. I was cofused when It worked for the both date formats. And the reason was, as anhn said, it correctly converted the date where day is > 12. When I tested it for day < 12, it failed. Sorry for the messing up.
Ok sajankk hope you didnt get cofused of what I said. you have to use either mm/dd/yyyy or YYYY-mm-dd for your querry.

[Al42]

am not getting the exact records as i expecting. is anything wrong in this query. and even i tried >=dtpicker1.value and <=dtpicker2.value. This too not working

Are you missing a few records from date2, but the rest return as expected? If so, try

"> #" & DateDiff("d", 1, dtpicker1.Value) & " # AND < #" & DateDiff("d", -1, dtpicker2.Value) & "#

That will account for the time also. (DateTime fields store the time as well as the date. Selecting all records up to dtpicker2 will only only return records with a date/time up to dtpicker2.Value 12:00:00 AM)