Infernal date handling in SQL !!!!

[hawker]

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 ?????

[BruceG]

I would change the Format part to "mm/dd/yyyy" and see if you get the results you expect.

[hawker]

No, this doesn't work. There must be a way of doing this !!

[Gary.Lowe]

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#.

[Mongo]

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:

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