datetime filter query

[Guest]

Hello,
I am using MS Access as a front end to SQL Server database tables.
In one of my tables i've got two columns formated as DateTime with the date
and time in each of them. Each time i query them for a certain date range i
get the correct months and days but all of the years instead of just, say,
2005.
Using the queries in MS Access I am trying to format the date time column to
display date only so that I can query the table for certain date ranges
without necessitating the time in the parameters but I'm not retreiving any
records.
Summary:
Before
columns datatype Date/Time: XX/XX/XX 00:00:00.000
query >11/01/05 00:00:00 produces all years, months greater than november
and days greater than the first.

after formating using FormatDateTime(thedate, 2)
query >11/01/05 produces no data.

Is there a better way to do this?

 

[Pat Hartman\(MVP\)]

The Format functions turn a date into a string. Range checking will not
work on a string date unless it is in the format yyyy/mm/dd.

The solution is to use the DateValue() function. It extracts just the date
portion of the date/time field.

Where DateValue(YourDate) > #11/01/05#