Using IIF in a Query

[sandybam]

Hi All,

I have a search form named SV Search Menu, which includes the following fields:

Agency Number
Name
State
Rep
cboFederalBuilding

All of the fields are required for data entry except the cboFederalBuilding field. What I am trying to do is run a query using the search form criteria where if the cboFederalBuilding field on the search form is null the query will return all records satisfying the rest of the query criteria. However, if the cboFederalBuilding field on the search form is not null the query will use that value in conjunction with the rest of the query criteria. Here is what I have tried as cboFederalBuilding field query criteria:

IIf((IsNull([forms]![sv search menu]![cbofederalbuilding])),Like "*" Or Is Null,[Forms]![SV Search Menu]![cboFederalBuilding])

When I run the query I receive an error message stating that the query is too complex. Separately, these statements return what I am looking for, but they do not work together.

Any suggestions on what I am doing wrong?

Thank you,
Sandy

 

[scriverb]

You were close. Just modify the code to return the queries column/field name as the criteria if IsNull. This way whatever the record has as its value it will compare against itself.

IIf((IsNull([forms]![sv search menu]![cbofederalbuilding])),[[i][red]column field name[/red][/i]],[Forms]![SV Search Menu]![cboFederalBuilding])

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[sandybam]

Bob,

Thank you for such a quick response. When I use this code the query returns all records that satisfy the query criteria and has a value in the cbofederalbuilding field. Since this is not a required field some records will have a Null value for this field. I would like to be able to have the records with a null value for this field returned as well when the first part of this IIF query is true. Is this possible?

Thank you!
Sandy

 

[scriverb]

You had it right in your first attempt:

IIf((IsNull([forms]![sv search menu]![cbofederalbuilding])),[column field name][red] or Is Null[/red],[Forms]![SV Search Menu]![cboFederalBuilding])

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]