Trying to check dates in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GaryP - 18 Apr 2008 15:05 GMT

Can anyone tell me why the following is returning only 0's? The return should
be mostly 1's based on the data in the fields it's checking. I also tried to
use the field names in the tables being queried to no avail.

Expr2: IIf([Hire Date]<1/1/2008 And [Termination Date]>1/1/2008,1,0)

Any help will be appreciated.

Thank you,

Gary

Reply to this Message

Klatuu - 18 Apr 2008 15:49 GMT

You need the date delimiter character (#) so the query knows for sure it is a
date
Expr2: IIf([Hire Date]<#1/1/2008# And [Termination Date]>#1/1/2008#,1,0)

Signature

Dave Hargis, Microsoft Access MVP

> Can anyone tell me why the following is returning only 0's? The return should
> be mostly 1's based on the data in the fields it's checking. I also tried to
[quoted text clipped - 7 lines]
>
> Gary

Reply to this Message

GaryP - 18 Apr 2008 15:57 GMT

That worked, Thank you

> You need the date delimiter character (#) so the query knows for sure it is a
> date
[quoted text clipped - 11 lines]
> >
> > Gary

Reply to this Message

John Spencer - 18 Apr 2008 16:23 GMT

Your dates need to be surrounded with # characters. If they are not
they will be treated as math expressions.

IIf([Hire Date]<#1/1/2008# And [Termination Date]>#1/1/2008#,1,0)

Also, since you are using date literals, the dates probably need to be
expressed in US form of MM/DD/YYYY format. Or use the unambiguous
YYYY-MM-DD format.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> Can anyone tell me why the following is returning only 0's? The return should
> be mostly 1's based on the data in the fields it's checking. I also tried to
[quoted text clipped - 7 lines]
>
> Gary

Reply to this Message