IIf Problem

[spock1971]

Guys

I have a database table with approx. 20 fields of which I want users to be able to choose from 7 of them. I've created lookup tables to enable dropdowns in a form and then a command button to open a query where the search criteria is populated from the chosen options on the form. However, I want the users to be able to choose 'All' in the dropdown and the query to then basically ignore that criteria. I've tried the following in one fo the fields and it doesn't work. I've spent hours trying different combinations all to no avail.

IIf([Forms]![Interrogation]![Combo0]="All",([Denials Database].[Category]) Like "*",[Forms]![Interrogation]![Combo0])

Any thoughts?

 

[cpuSmoker]

help

why use the explicit reference ?
ie: [form]![controlname]

Are you trying to reference these from another form?
Maybe you could output the selected choices to a buffer table possibly to avoid any confusion. If you sent a sample of what you are working on this would help.

 

[Jon K]

To use an IIF expression in the criteria of a query, you need to combine the field name with the IIF expression in the Field row like this:-

Field: IIf([Forms]![Interrogation]![cboCategory]="<All>", True , [Category]=[Forms]![Interrogation]![cboCategory])

Show: uncheck

Criteria: True


See sample database attached.


Note
The word True in the IIF expression will return every record including Null values.

To exclude Null values, you can use [Category] Like "*" instead of True.
.

 

[spock1971]

JonK

Your attachment doesn't unzip my end as it appears to be an archive file. I didn't fully understand the message.

Are you saying I should have a seperate column in my query for each of the 7 lookups where a TRUE response will appear if the user has chosen 'All'. If so, how would I then query on a TRUE response in the field of the database. Maybe the attachment will explain better if you can get another copy to me.

Cheers

D

 

[Jon K]

Yes, a separate column for each lookup field (as we can't put IIF expressions in the criteria row.)


If you have a YesNo field and you want to query a True response in the field, you can nest two IIF functions:

Field: IIf([Forms]![Interrogation]![cboYesNoField]="<All>", True, IIf([Forms]![Interrogation]![cboYesNoField]="True", [YesNoField]=True, [YesNoField]=False))

Show: uncheck

Criteria: True


I used "<All>" instead of "All" to ensure that it appears in the first position in the combo drop-down list.

I have added a numeric field and a yes/no field to the database. The A file was zipped using WinZip. The B file was zipped using 7-zip. Hope you can unzip one of them.
.

 

[spock1971]

JonK

Sorry,

Can't get either to open or your suggested extra fields to work. However in a seperate query I ask the user to enter either 'Denials' or 'Refusals' and the IIf statement converts it to look in the table for 'PD' or 'PR' so I can't understand why that's working and not the first.

Would it be possible to mail me the file or the SQL for the query you have working?

Mail address is darren.farrelly@travelodge.co.uk

Cheers

D

 

[Jon K]

The database has been sent to your email address.


The two SQL statements are:

SELECT ID, Category, AnotherField, NumericField, YesNoField
FROM tblData
WHERE IIf([Forms]![Interrogation]![cboCategory]="<All>",True,[Category]=[Forms]![Interrogation]![cboCategory])=True AND
IIf([Forms]![Interrogation]![cboAnotherField]="<All>",True,[AnotherField]=[Forms]![Interrogation]![cboAnotherField])=True AND
IIf([Forms]![Interrogation]![cboNumericField]="<All>",True,[NumericField]=[Forms]![Interrogation]![cboNumericField])=True AND
IIf([Forms]![Interrogation]![cboYesNoField]="<All>",True,IIf([Forms]![Interrogation]![cboYesNoField]="True",[YesNoField]=True,[YesNoField]=False))=True;


SELECT ID, Category, AnotherField, NumericField, YesNoField
FROM tblData
WHERE IIf([Forms]![Interrogation]![cboCategory]="<All>",[Category] Like "*",[Category]=[Forms]![Interrogation]![cboCategory])=True AND
IIf([Forms]![Interrogation]![cboAnotherField]="<All>",[AnotherField] Like "*",[AnotherField]=[Forms]![Interrogation]![cboAnotherField])=True AND
IIf([Forms]![Interrogation]![cboNumericField]="<All>",[NumericField] Like "*",[NumericField]=[Forms]![Interrogation]![cboNumericField])=True AND
IIf([Forms]![Interrogation]![cboYesNoField]="<All>",True,IIf([Forms]![Interrogation]![cboYesNoField]="True",[YesNoField]=True,[YesNoField]=False))=True;

.