Select Query Using either 1 of 2 fields but not both

[Jimmy211]

Hi,

I'm trying to get an access query to return records based on either one field or another field but not both. I've set up a form for the user to enter data with an option box with 2 values: if the user clicks the first option then a combo box with the values is enabled, if they choose the second option the first combo box is disabled and nulled, and a second combo box is enabled with its values.

My problem comes in using a query to determine which of the options is selected and then filtering the records to match.

The two fields are:
Area
Lact

In the query I’m using and IIF statement in the criteria section of each field that looks like this:
Area : IIf([forms]![PipeUpdateForm]![BilledBy]=1,[forms]![pipeupdateform]![area])
Lact: IIf([forms]![PipeUpdateForm]![BilledBy]=2,[forms]![pipeupdateform]![lact])
Where [BilledBy] is the option box.

The trouble with this is that by not specifying the False value of the IIF statement, it assumes it to be null. I would like it to NOT use the criteria if the IIF statement evaluates as false. Each criteria expression works fine by itself, but not together.

I would appreciate any help.
Thanks
James

 

[x56mike]

James,

Since the IIF statements will always evaluate to something (value or null), its impossible not to have a criterion in one of the fields. So, I suggest that you make two queries, one for each criteria option. Then whenever the query is invoked (I'm assuming on an event of the form), simply use an if statement in code to determine which query is run based on the option that the user has selected.

 

[gol4]

Look into the switch function

if that is not what you have in mind check out his link shows how to use multiple comboboxes but you can apply to basically any control

http://www.fabalou.com/Access/Queries/comboboxes_criteria.htm