IIF Statement issue in parameter query (1 Viewer)

pepetheshrimp1 · Aug 11, 2019

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 · Aug 11, 2019

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 · Aug 11, 2019

you remove .Value from your query.

Code:

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

pepetheshrimp1 · Aug 11, 2019

NauticalGent said:
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 · Aug 11, 2019

arnelgp said:
you remove .Value from your query.

Code:

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 · Aug 11, 2019

there is an extra ) on the first mechanism:

Code:

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

NauticalGent · Aug 11, 2019

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 · Aug 11, 2019

arnelgp said:
there is an extra ) on the first mechanism:

Code:

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 · Aug 11, 2019

NauticalGent said:
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.

IIF Statement issue in parameter query (1 Viewer)

pepetheshrimp1

New member

NauticalGent

Ignore List Poster Boy

arnelgp

..forever waiting... waiting for jellybean!

pepetheshrimp1

New member

pepetheshrimp1

New member

arnelgp

..forever waiting... waiting for jellybean!

NauticalGent

Ignore List Poster Boy

pepetheshrimp1

New member

pepetheshrimp1

New member

Similar threads

Users who are viewing this thread