how to compare two date fields

[Summer123]

Hello,
why isnt this query working?

SELECT name, Date1, Date2
FROM table1
WHERE Date1 > DATE2;

values in the table itself
Name Date1 Date2
John Smith 08021977 07281978
Paul test 03142011 06061963

should return the below correct? Since date1 is greater then date2 (08021977 is earlier then 07281978)

Name Date1 Date2
John Smith 08021977 07281978

but when i write the above SQL it returns nothing... can someone please help? I've tried "<" as well, when i do that then both of the rows above come (John and Paul)...

 

[spikepl]

An earlier date is "smaller" than later date.

What is the data type of field Date1 and Date2 ?

 

[Summer123]

thank you, its text

 

[spikepl]

to compare dates as dates, the fields need to be of type date

 

[Brianwarnock]

Spikepl is absolutely correct, however what I don't understand is that in a Text field comparison 08021977 should be > than 07281978 , has Date1 got a leading space?

Brian

 

[Summer123]

no no leading spaces... i am still not certain why it wont simply give the greater value.. confusing.. so then i went and created an "make table" by doing below

Year: Right([02D_Record]![Birth Date],4)
Month: Left([02D_Record]![Birth Date],2)
Day: Mid([02D_Record]![Birth Date],3,2)

Expr1: DateValue([month] & "/" & [day] & "/" & [year])

and then took that table and created another query to compare the 2 date fileds - still the same issue...

 

[GohDiamond]

No need to make another table, Just convert the datestring to a real date using DateSerial() with one query then Query that First Query with your criteria.

Query1 ("DateTest" should be replaced by the Table Name you are querying)

SELECT DateTest.Name, DateTest.Date1, DateTest.Date2, DateSerial(Right([date1],4),Left([date1],2),Mid([date1],3,2)) AS DateSerial1, DateSerial(Right([date2],4),Left([date2],2),Mid([date2],3,2)) AS DateSerial2, IIf([DateSerial1]<[DateSerial2],"Y","N") AS [Select]
FROM DateTest;

then Use a 2nd query to get your results from your criteria,

SELECT [DateTest Query].Name, [DateTest Query].Date1, [DateTest Query].Date2, [DateTest Query].DateSerial1, [DateTest Query].DateSerial2, [DateTest Query].[Select]
FROM [DateTest Query]
WHERE ((([DateTest Query].DateSerial1)<[DateSerial2]));


The results of Query 2 are:
Name Date1 Date2 DateSerial1 DateSerial2 Select
John Smith 08021977 07281978 8/2/1977 7/28/1978 Y = Date1 is earlier (an older date) than Date2 (a newer date)

Cheers!
Goh

 

[Summer123]

Genius!! thank you soo much!

 

[Brianwarnock]

A couple of points
1 Don't use Access reserved words as object names eg SELECT
2 Avoid spaces in object names
3 You can dothis in one query, not sure why you need to return the Y as that is the only result than can be returned.

to do it all in the first query

SELECT DateTest.Name, DateTest.Date1, DateTest.Date2, SELECT DateTest.Name, DateTest.Date1, DateTest.Date2, DateSerial(Right([date1],4),Left([date1],2),Mid([date1],3,2)) AS DateSerial1, DateSerial(Right([date2],4),Left([date2],2),Mid([date2],3,2)) AS DateSerial2
Where DateSerial(Right([date1],4),Left([date1],2),Mid([date1],3,2)) < DateSerial(Right([date2],4),Left([date2],2),Mid([date2],3,2))
FROM DateTest;

You decide that to show the text and actual dates is overkill.

Brian

 

[Summer123]

thank you Brian. i will give this a try as well. thank you both though.