Comparing dates in a query

[elaineva]

I have a problem comparing dates in a query. I’m using Access 2002 and the “date” fields I’m comparing were text fields. I’ve converted them to date fields using CDATE.

Example: IIF ([Acceptdate]>=[RejectStart],[Acceptdate],[Rejectdate])

Example of Acceptdates: 06/01/2003, 06/12/2004, 07/01/04

Example of RejectStart: 06/01/2004

Based on my IIF statement – I would expect only 6/12/2004 and 07/01/2004 to be returned – instead I also get the 06/01/2003.

I’m sure there must be a very simple explanation – but I can’t seem to figure it out.

Please help……

 

[lupins46]

What is the value of RejectDate?

 

[PHV]

In table design view, what is the type of Acceptdate and Rejectdate ?
Have you still an Acceptdate displaying like this 07/01/04 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[elaineva]

Sorry....

Rejectdate = 01/01/2004

 

[elaineva]

date shows as 07/01/2004 - typo...

 

[lupins46]

I agree with you that on the face of it there is no sense in your result. Even if the dates wer still text then in this case the results should still be as you are expecting.

Is the CDate() conversion in the same query as this IIF?

Is your date in mm/dd/yyyy or dd/mm/yyyy?

Add another few columns into your query to check things:
myMM:month(Acceptdate)
myDD:day(Acceptdate)
myMMR:month(Rejectdate)
myDDRay(Rejectdate)

See if these produce the results you expect.