using date expressions in IIF functional query

S

Steve

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.
 
J

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 ...
 
S

Steve

John:

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


John Spencer said:
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 ...


Steve said:
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.
Click to expand...
Click to expand...
 
Top