Mixing Number and Text Query Criteria from a Form

[MCTampa]

I have a form with an option group from which a user will select one of the following parameters:
Resort Code (a three letter code)
Area Code (a three digit code)
Resort Region (a text string)

They must select one and can only select one of the elements.
The selection choice is output to a text box.
The text box is reading a different part of the form and is correctly returning the selection.

For example, the selection could say "XYZ" (Resort Code), or 999 (Area Code) or "The North" (Resort Region).

At this point, everything is working well.

I have a query with three fields match the above. One field for Resort Code, one field for Area Code and one field for Resort Region.

Here is my problem:
Under Resort Code, my criteria formula is: IIf([Forms]![Existing Resort]![DestinationChoice]=[forms]![Existing Resort]![ResortCode],[forms]![Existing Resort]![ResortCode],Null)
Under Area Code, my criteria formula is: IIf(Val([forms]![Existing Resort]![DestinationChoice])=[forms]![Existing Resort]![AreaCode],[forms]![Existing Resort]![AreaCode],Null)
I haven't yet moved on to Region, but it would be the same thing.

I believe my problem is the null at the end of the formula.
What I want is a formula for Resort Code to say "If the value in my destination choice doesn't match the resort code on the form, then essentially ignore this value"

The same for Area Code.

This way, ultimately, either the criteria for Resort Code or the criteria for Area Code or the criteria for Region will dictate the query results.
I'm trying to keep it all in one query so the user only have to run one macro.

 

[welshgasman]

I believe you use OR for the Null control

[Forms]![Existing Resort]![DestinationChoice]=[forms]![Existing Resort]![ResortCode] OR [forms]![Existing Resort]![ResortCode] IS Null

 

[Micron]

Use OR within an immediate IF ( Iff )? Or did you mean for the criteria in the query field use
[forms]![Existing Resort]![ResortCode] OR Is Null

 

[MCTampa]

I believe you use OR for the Null control

[Forms]![Existing Resort]![DestinationChoice]=[forms]![Existing Resort]![ResortCode] OR [forms]![Existing Resort]![ResortCode] IS Null

This resulted in me returning all of the resort codes in my database.

I should have probably added a point of clarity here:

On my initial form, the user enters a resort code and presses the Update button. This trigger a Dlookup of all of the pertinent information related to that resort such as the full name, area name, area code, region, etc.

Further down in the form, the user is asked if they want to run the analysis against either the historical data for the Resort, Area or Region.

If they select the Resort option button, the resort code populates in a text box.

In my query criteria, under the resort code field, I'm performing the analysis (formula) to say "if the value in the text box matches the resort code on the form, use this code." However, if the if the user selects the Area option button, I want the criteria under the Resort code to either be ignored OR I'm thinking it could return all values, which would be limited because the criteria under Area would take priority.

 

[MCTampa]

I also tried this: IIf([Forms]![Existing Resort]![DestinationChoice]=[forms]![Existing Resort]![ResortCode],[Forms]![Existing Resort]![ResortCode],Is Null) Or Like "*"
But even when Resort is selected, I return all the data.

 

[MCTampa]

For what it's worth, this happened to be the way I got it to work.