IIF Statement issue in parameter query

[pepetheshrimp1]

Hi

If someone could help me with the following query that would be great.

I have created a form which I am using to filter a query. In the query I have created an expression shown below which works fine - giving me the value 1 if the combo box mechanismcbo is blank or has the relevant value.

Mechanisms: IIf(IsNull([Forms]![frmGameSuggestion]![mechanismcbo]) Or (Mechanism.Value)=([Forms]![frmGameSuggestion]![mechanismcbo]),1,2)

The problem is that I would like to include a second combo box so that the user can choose between two mechanisms. As soon as I try to include the second combo box in the expression I get the warning that a bracket or parantheses is missing. I have tried to resolve this for the past hour but have failed. I suspect the method I am attempting is not the most elegant solution
so any help in advising me what I need to change in the below code would be much appreciated. The code I have attempted to use is below:

Mechanisms: IIf((IsNull([Forms]![frmGameSuggestion]![mechanismcbo]) Or (Mechanism.Value)=([Forms]![frmGameSuggestion]![mechanismcbo]),1, IIf((IsNull([Forms]![frmGameSuggestion]![mechanismcbo1]) Or (Mechanism.Value)=([Forms]![frmGameSuggestion]![mechanismcbo1]),1,2)

Many thanks in advance

Sam Jones

 

[NauticalGent]

Ciao SaJo,

I am a little thick so forgive me if I ask an obvious question. It appears you want to filter a form based on two combo boxes which are on that form.

If I have that correctly, I do not understand the source of the first combo box. Can you elaborate?

 

[arnelgp]

you remove .Value from your query.

Mechanisms: Switch(Nz([Forms]![frmGameSuggestion]![mechanismcbo], [Mechanism]) = [Mechanism]),1, 
Nz([Forms]![frmGameSuggestion]![mechanismcbo1], [Mechanism]) = [Mechanism], 1, True, 2)

 

[pepetheshrimp1]

Ciao SaJo,

I am a little thick so forgive me if I ask an obvious question. It appears you want to filter a form based on two combo boxes which are on that form.

If I have that correctly, I do not understand the source of the first combo box. Can you elaborate?

Hi NauticalGent

Many thanks for replying. Both combo boxes are unbound controls in the form frmGameSuggestions.

In the primary table (tblGameStats) the Mechanism field stores multiple mechanisms (controlled by a lookup table) so there is multiple mechanism values for most records. Because this field stores multiple values I use
Mechanism.Value in the code in the query rather than just Mechanism.

I hope the above makes sense.

Kind regards

Sam

 

[pepetheshrimp1]

you remove .Value from your query.

Mechanisms: Switch(Nz([Forms]![frmGameSuggestion]![mechanismcbo], [Mechanism]) = [Mechanism]),1, 
Nz([Forms]![frmGameSuggestion]![mechanismcbo1], [Mechanism]) = [Mechanism], 1, True, 2)

Many thanks for your reply Arnelgp

Sorry I should have mentioned that the Mechanism Field is a multiple entry field so I have found that instead of using just Mechanism I have had to use Mechanism.Value.

I did just try your suggestion using Mechanism and Mechanism.Value but for some reason I get a warning "the expression has a function with the wrong number of arguments".

Kind regards

Sam

 

[arnelgp]

there is an extra ) on the first mechanism:

Mechanisms: Switch(Nz([Forms]![frmGameSuggestion]![mechanismcbo], Mechanism.Value) = Mechanism.Value, 1, 
Nz([Forms]![frmGameSuggestion]![mechanismcbo1], Mechanism.Value) = Mechanism.Value, 1, True, 2)

 

[NauticalGent]

It sounds like the BuildCriteria() method is what you need. Give this link a read:

https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb237783(v=office.12)

*** Edit ***

This is a less arcane approach in my opinion without any VBA and more along the lines of how you are going about this:

https://youtu.be/CTiA_4Me0cI

 

[pepetheshrimp1]

there is an extra ) on the first mechanism:

Mechanisms: Switch(Nz([Forms]![frmGameSuggestion]![mechanismcbo], Mechanism.Value) = Mechanism.Value, 1, 
Nz([Forms]![frmGameSuggestion]![mechanismcbo1], Mechanism.Value) = Mechanism.Value, 1, True, 2)

Many thanks for your help Arnelgp

 

[pepetheshrimp1]

It sounds like the BuildCriteria() method is what you need. Give this link a read:



*** Edit ***

This is a less arcane approach in my opinion without any VBA and more along the lines of how you are going about this:




Fantastic - thanks NauticalGent, I will give these a read.