-
I have a date/time field in my access database (ie stored like 06/07/00 12:00:00) and I wish to do an SQL to find certain dates (ie. ignoring the time part of the data) I'm doing this:
sSqlTxt="SELECT * FROM Letters WHERE DateSent = #" & Format(dtFrom.Value, "DD-MMM-YYYY") & "#"
There are many records that match this, but this sql is not picking up anything. I think is something to do with the fact that it is comparing a Date/Time with just a date.
I need the data in the database to contain the time with the date, but as far as this particular SQL goes, I just want to compare dates.
Anyone know where am I going wrong ?
Why is date handling within databases SO FIDDLY ?????
-
I would change the Format part to "mm/dd/yyyy" and see if you get the results you expect.
-
No, this doesn't work. There must be a way of doing this !!
-
Hawker as Brucy said change the format to mm/dd/yyyy.
SQL seems to prefer it in this manner.
If you have access when you create a query with dates in them i.e. #01/02/2000#, you you can see how the SQL in SQl view changes the format to #02/01/2000#.
-
Think about your SQL statement for a minute. Is it surprising no records match midnight for the date you ask for?
Try something like this, instead:
Code:
Dim sql_str As String, dt1 As String, dt2 As String
dt1 = Format(dtFrom.Value, "#DD/MM/YYYY# ")
dt2 = Format(DateAdd(d, 1, dtFrom.Value), "#DD/MM/YYYY#")
sql_str = "SELECT * FROM Letters " & _
"WHERE DateSent > " & dt1 & _
"AND DateSent < " & dt2
