General Date format

In a MSAccess database I have a date column in the General Date format dd/MM/yyy HH.mm.ss and two DTPickers with the same custom format.

In the following routine:


private Sub Command1_Click()

Adodc1.RecordSource = "SELECT date FROM " & _
"Table1 WHERE date BETWEEN #" & _
DTPicker1(0).Value & "# AND #" & _
DTPicker1(1).Value & "# ORDER BY date"

set DataGrid1.DataSource = Adodc1

End Sub




for some reason the filter ignores the hour value of the second DTPicker. For example with DTPicker values of 1/1/1999 12.00.00 and 2/1/1999 12.00.00 the filter selects all records for 1/1/1999 that fall after 12.00.00 and all records for 2/1/1999 no matter what the hour value is.

Any ideas as to why this is happening?

Thanks,

Andrew

>for some reason the filter ignores the hour value of the second DTPicker.
>For example with DTPicker values of 1/1/1999 12.00.00 and 2/1/1999
>12.00.00 the filter selects all records for 1/1/1999 that fall after
>12.00.00 and all records for 2/1/1999 no matter what the hour value is.

It sounds to me like the format for your dates is getting mixed up - are you sure that the database is interpreting your dates as DD/MM/YYYY ?

Try converting the dates in the SQL statement to '01-JAN-1999' and '02-JAN-1999' or a similar format so you can check the date values.



Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb

Hi Chris. I ran some more checks and it turns out that you were right. So what I ended up doing is creating 2 variables and assigning them the values of the DTPickers with the days and months reversed.

A friend of mine suggested hard-coding the dates in the SQL instead of using a variable. Do you know anywhere that I could look for examples on how to do that?

Thanks,

Andrew

I wouldn't worry too much about that - as long as you know what format your database expects the dates to be in (or better still, convert them using stored procedures / t-sql), then it shouldn't matter.

As a rule, I always tend to use dates internally in programs in a YYYY/MM/DD format so that I always know they will be handled correctly, that way you don't get the 1/1/1999 and 2/1/1999 problem that you experienced. The only problem you have then, is displaying them to the user in the format that they expect.

Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb