Trying to check dates in a query

  • Thread starter Thread starter GaryP
  • Start date Start date
G

GaryP

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
 
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)
 
That worked, Thank you

Klatuu said:
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)
--
Dave Hargis, Microsoft Access MVP


GaryP said:
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
Click to expand...
Click to expand...
 
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
'====================================================
 
Back
Top