Question Problem with IIF & sorting records based on conditional user input

[samdatum]

Access 2010, Win7:

I'm using an expression in a parameter query to determine the sort order. Actual expression:

IIf([Enter Rep#]=1,IIf([Randomize Rep1?]="N",[tblPedigrees.Type_Cross]),Rnd([SSID]))


The query fails when [Enter Rep#]>2. The [Randomize Rep1?] parameter still appears even though conditions should have been met initially with [Enter Rep#]>1.

Could anyone help direct me toward a solution or correct syntax?
Thanks Much!

 

[spikepl]

Looking things up in the documentation is always a good starting point:

http://office.microsoft.com/en-us/access-help/iif-function-HA001228853.aspx
1.
Argument Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.

2.
IIf always evaluates both truepart and falsepart, even though it returns only one of them.

Pick your parameters up from textboxes or comboboxes on a form.

 

[Pat Hartman]

In SQL, the IIf() evaluation follows only the "true" path. All other expressions are ignored. However, in VBA, all conditions are evaluated not just the "true" path so you have to be more careful with your expression to avoid divide by zero which is usually the culpret.

The query fails when [Enter Rep#]>2. The [Randomize Rep1?] parameter still appears even though conditions should have been met initially with [Enter Rep#]>1.

Your IIf() looks for =1 not >1. Was that a typo or is it the problem?

 

[samdatum]

I rewrote it this way. I was thinking that I couldn't use the AND with IIF but it improved the statement. In any case, one must still tab through the parameter in "false" part of IIF but user response has no impact as intended.

IIf([Enter Rep#]=1 And [Randomize Rep1?]="N",[tblPedigrees.Type_Cross] & [tblPedigrees.Cross_No] & [tblPedigrees.SelNo],Rnd([SSID]))

I appreciate those of you took the time to read & respond. Thank you!