iif seems to be iiffy!

[jimleon]

Hi guys,
I am using 2 queries (startdate & lastdate) to input some dates to be used as criteria in a 3rd query.

The expression below returns the value of startdate which will be either todays date or some past date. If the returned date is today it should return a null and exit.

expr1: IIf([startdate].[Expr1]=Date(),Null,[Enter 2nd date:ie 01/01/2007])

However it prompts for the [Enter 2nd date:ie 01/01/2007] vaiable even if the test condition is true.!!!!!

Is this a function of the iif statement...

Cheers Jim

[jimleon]

OK, I found another thread on this site saying iif evaluates both states regardless of the condition! Dooh.
So does anyone know how to set up a conditional input where the prompt box is only displayed if the criteria is met?

Many thanks in advance.

[MMcCarthy]

Hi Jim

Don't know if this will work but it's worth a try.

expr1: nz(IIf([startdate].[Expr1]=Date(),"",Null),[Enter 2nd date:ie 01/01/2007])

Mary

[jimleon]

Sorry, even with NZ it will still attempt to perform both conditions :o(

[MMcCarthy]

Hi Jim

Don't know if this will work but it's worth a try.

expr1: nz(IIf([startdate].[Expr1]=Date(),"",Null),[Enter 2nd date:ie 01/01/2007])

Mary

Yes but in this case the conditions are "" and Null so will only go to prompt when null returned.

[jimleon]

The iif command will ALWAYS evaluate both options before returning the correct one!

I need a query based conditional input prompt.

[NeoPa]

The Jet SQL Engine (Any SQL engine really, as far as I know), will always request values for ALL parameters within the SQL, regardless of whether or not they actually affect any of the results.
In this case, this is not an issue with IIf() (Or Nz() etc...). In fact, within SQL, IIf() doesn't actually evaluate both sides as you would expect if using it from within VBA code.