using date expressions in IIF functional query

[Guest]

The following error is not addressed anywhere. The following formula does
not recognize 2 digit months as being greater than the conditional expression.

Life Cost: IIf([As of date:]<=#5/31/2005#,(([Sal Life Benefit]+[Life
Benefit])*0.000143),(([Sal Life Benefit]+[Life Benefit])*0.000183))

Using a date of 12/1/2005 produces a True result.

 

[John Spencer]

Try forcing the type of As Of Date: by surrounding it with CDate.

IIF(CDATE([As of Date<= ...

If [As of Date:] is a parameter you could also declare its type

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

In the SQL view
PARAMETERS [As of Date:] DateTime;
SELECT ...

 

[Guest]

John:

The CDATE solution worked.
--
THANKS!!!!!!!!!!!
Steve

Try forcing the type of As Of Date: by surrounding it with CDate.

IIF(CDATE([As of Date<= ...

If [As of Date:] is a parameter you could also declare its type

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

In the SQL view
PARAMETERS [As of Date:] DateTime;
SELECT ...

The following error is not addressed anywhere. The following formula does
not recognize 2 digit months as being greater than the conditional
expression.

Life Cost: IIf([As of date:]<=#5/31/2005#,(([Sal Life Benefit]+[Life
Benefit])*0.000143),(([Sal Life Benefit]+[Life Benefit])*0.000183))

Using a date of 12/1/2005 produces a True result.