I've got a table in a sql server db that's linked to access. i'm trying to run a query where one of the criteria is date related. I've checked to verify that the field is date/time datatype. and the data is in the format of 2/28/2006 8:51:43 AM
In the criteria box, if I type in <#3/2/2006# Or >#3/4/2006# it seems to work and filters the data. Also, this works too: > #3/10/2006# It returns the correct records.
However, if I use #3/6/2006# (or with an = sign) it returns zero rows and I know there is data on that date since it was returned on the other query.
The main problem is with the date() function. It doesn't work at all. Date(), date()-2 between date() and date()-14 all return zero rows.
Just to let you know, this is the same table I have another thread open about data showing up incorrectly. When I run the query, the data that shows up is completly wrong. I have to export it into an excel file and then open it to read the correct data.
Since I posted this a few hours ago, i've been searching and reading anything that might help.
I formated the date time field with this. Start: Format([start date],"dddd"", ""mmm d yyyy") It returned the dates formated the correct way. I then added date() into the criteria box. zero returned.
Just for the heck of it, I removed the date() function and set the sort order of the start date to ascending and ran it again. It sorted in alphabetically by the day of the week. Is that correct? shouldnt it sort by the actual date, meaning earliest date first? Could this mean that somehow access is not recognizing it as a date/time datatype? and that's why the date() function is returning nothing?
But then, why would between #3/2/2006# and #3/10/2006 work and return the correct data?
I had a similar problem with date/time and I opened a support call with Microsoft, here is the responce:
Microsoft Access stores the Date/Time data type as a double-precision, floating-point number (up to 15 decimal places). The integer portion of the double-precision number represents the date; the decimal portion represents the time.
Valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). A date value of 0 represents December 30, 1899. Microsoft Access stores dates before December 30, 1899 as negative numbers.
Valid time values range from .0 (00:00:00) to .99999 (23:59:59). The numeric value represents a fraction of one day. You can convert the numeric value into hours, minutes, and seconds by multiplying the numeric value by 24.
To view how Microsoft Access stores Date/Time values as numbers, we can use the Cdbl() function. Cdbl() is a Visual Basic function which can convert a date/time value to a double number.
Cdbl(#2/28/2003#) = 37680
Cdbl(#2/28/2003 3:53:45 PM#) = 37680.6623263889
As you can see the real value stored in the database for #2/28/2003# is less than from #2/28/2003 3:53:45 PM# and that's way we cannot see expected records in the query.
This article that I sent to you in my last email explains how Microsoft Access stores the Date/Time data type, and why you may receive unexpected results when you calculate or compare dates and times.
210276.KB.EN-US ACC2000: Storing, Calculating, and Comparing Date/Time Data
To make sure we try to compare the date part of the Order_Date Date/Time field, we can use the DateValue() function. The DateValue() function returns an integer number representing the date but not a fractional time value. For example:
DateValue(#2/28/2003 3:53:45 PM#) = 2/28/2003
So we can use this function in the query to create a calculated field as listed below:
Order Date: DateValue([Order_Date])
thanks much for the reply and the info. I finally figured out how to format the date field so that date() would work. I used Start: Format([start date],"m/d/yyyy") that formated it with the date only and then the date() function worked.
The info you gave me explained why it wasn't working before. It was the time fractions that were throwing it off. I'll keep that in mind in the future.