IIF function as query criteria (1 Viewer)

[mikeTTI]

I am trying to use an IIF function in each of three yes/no fields in a query to select records where one of the yes/no fields (as determined by a dialog form) = true, regardless of the value of the other two fields. Each record can contain any combination of Trues and Falses in these three fields.

The field to be evaluated will be determined based on a combo box on a dialog form. For example for the SALproc field the criteria cell contains: -

=IIf([Forms]![LevyReportDialog]![DialogCombo]="SAL","True","*")

There are corresponding functions in the criteria cell for the otehr two fields

So when I am reporting for SAL I can include all records where SALproc = true, regardless of whether the other fields are true or false.

I can get these functions to work in three separate text boxes on the dialog a form, and was feeling quite clever, but I can't get them to work as query criteria. I get an error message saying it is typed incorrectly or too complex.

I have also tried using the IIF functions in text boxes on the dialog form, and then using the value of those text boxes as criteria in the query. I get the same error message.

I can also get the query to select the records I want by manually entering True for the SALproc field and * for the other two yes/no fields, so the concept of using some combination of true, *, * as criteria appears to be valid.

I would really appreciate some help on this, I suspect I am not far away from making it work.

I'm assuming that it is actually possible to use an IIF function as a query criteria ... ...

In the meantime I am going to try creating functions containing the IIF functions and use those as criteria.

 

[Jon K]

The field to be evaluated will be determined based on a combo box on a dialog form. For example for the SALproc field the criteria cell contains: -

=IIf([Forms]![LevyReportDialog]![DialogCombo]="SAL","True","*")

There are corresponding functions in the criteria cell for the otehr two fields

So when I am reporting for SAL I can include all records where SALproc = true, regardless of whether the other fields are true or false.

See the attached database. It assumes the other two Yes/No fields are called ABCproc and XYZproc and the combo box contains the three options SAL, ABC and XYZ for user-selection.

Hope it's what you are after.
.

 

[mikeTTI]

Thanks Jon K, that is very kind of you yo go to so much trouble.

That works perfectly.

Thanks

Mike

 

[rohiiit]

IIF Function Problems

Hello guyz,

IIf(IsNull([JCR - Act Lab_Crosstab Hr]![CP]),0,[JCR - Act Lab_Crosstab Hr]![CP])

I am trying to use the following expression. Problem is [CP] exists sometimes, but sometimes not in [JCR - Act Lab_Crosstab Hr]. How can I put an If statement in this expression so that even if cp doesnt exsist, the querry will run?

Please help!!!

Ro

 

[Rabbie]

Remember that the iif function always evaluates both the true and the false value everytime it is called. This means that you have to handle the case when [CP] does not exist. Read up on the NZ function. This may help you

 

[rohiiit]

thanks Rabbie, I am not sure how to put Nz command in the followin expression? Can you help please?

IIf(IsNull([JCR - Act Lab_Crosstab Hr]![CP]),0,[JCR - Act Lab_Crosstab Hr]![CP]).

 

[rohiiit]

i used this fucntion but access styll says tht CP doesnt exist

Nz([JCR - Act Lab_Crosstab Hr]![CP],0)

 

[sadr110]

its true
like IIf([Forms]![LevyReportDialog]![DialogCombo]="SAL","True","*")

 

[jordinho]

help

i need some help with IF statements in access. I have been asked to set up a query to calculate total duration in additional learning support.

the scenario is if the number of hours is less than 13 then that student is in band one. and if its greater than 13 its band two and so on. This is what my formula looks like so far:

=IIf([Total Duration]="13<","Band One","Band Two")

can you notify me of any problems why it may not be working

 

[namliam]

@Jordinho

Please only post your question in one place at a time. At some point someone will come by and answer it for you.
See: http://www.access-programmers.co.uk/forums/showthread.php?t=113457