Not getting the proper data when comparing with dates in MSACCESS

[Thilaka]

Have created a table with different fields which includes date as one fields

when i executed the following query, i am not getting the data whose date is less than the specified date.

SELECT *
FROM shiftdetails
WHERE format(Date_of_Shift,'mm/dd/yyyy')<'1/1/2007'
ORDER BY [shiftdetails].[Date_of_Shift] DESC;


can anybody help me

[AricC]

I think this is more of an Access question I will move your thread their.

Aric

[Rabbit]

Have created a table with different fields which includes date as one fields

when i executed the following query, i am not getting the data whose date is less than the specified date.

SELECT *
FROM shiftdetails
WHERE format(Date_of_Shift,'mm/dd/yyyy')<'1/1/2007'
ORDER BY [shiftdetails].[Date_of_Shift] DESC;


can anybody help me

I wouldn't suggest using strings to compare dates. If, for some reason, you need to compare dates using strings, you'll need to use "yyyy/mm/dd" as the format. For single digit months and days, you'll need to append a 0 in front.

--> format(Date_of_Shift, 'yyyy/mm/dd') < '2007/01/01'

The problem with strings and dates is that it compares the characters starting from left to right. What I suggest is to compare using date values.

--> Date_of_Shift < #1/1/2007#

[ADezii]

Have created a table with different fields which includes date as one fields

when i executed the following query, i am not getting the data whose date is less than the specified date.

SELECT *
FROM shiftdetails
WHERE format(Date_of_Shift,'mm/dd/yyyy')<'1/1/2007'
ORDER BY [shiftdetails].[Date_of_Shift] DESC;


can anybody help me

For your comparison to work [Date_of_Shift] must be defined as Date/Time and it cannot be compared to a String as previously indicated (#1/1/2007# should be used instead of '1/1/2007')