True/False IIf statement with a combo box parameter

C

Chris

I have a combo box form created where the user can select among several
options (employee levels or titles).

I have a table of records with a checkbox/yes/no column on whether or not it
applies to a certain employee level.

I want a query that will show all records that are checked and that apply to
that employee level.

So, If the user selects lets say "manager" from the combo box form, the
query will show all records in which that column "Applies to Manager" is
checked as a yes. If they pick a different employee level, the manager data
should not appear.

Here's the criteria I currently have in the query:
IIf([Forms]![Employee Level Form]![Level]="Manager",True)

in SQL:

SELECT Chart.[Applies to Managers], Chart.[Manager Training Level]
FROM Chart
WHERE (((Chart.[Applies to Managers])=IIf([Forms]![Employee Level
Form]![Level]="Manager",True)));

Any help is appreciated
 
C

Chris

I'm going to expand on this a bit further.

I have 5 checkbox columns in my table, 1 for managers and 1 for lets call
them Position1 Position2 Position3 and Position4. The user should be able to
pick one of those positions from the combo box, and the query needs to return
those records where that position's box is checked.

I was thinking it would be simply putting IIf([Forms]![Employee Level
Form]![Level]="PositionX",True) in each of the columns criteria, but thats
not working.
 
C

Chris

NEVERMIND! I figured it out. I knew it was going to be so simple I didn't
see it. The [Forms]![Employee Level Form]![Level] should have read
[Forms]![Employee Level Form]![cboLevel]
 
K

Klatuu

I would add the False part of the IIf statement for clarity:
IIf([Forms]![Employee Level Form]![Level]="Manager",True, False)

Now, the real problem is a flaw in database design. It appears from you
post that only one of the field can be true. If this is correct, then rather
than 5 Boolean fields, you should have one numeric field that contains a
value that corresponds to the position level. Then on your form use either
a Combo or an Option Group instead of 5 check boxes.

Chris said:
NEVERMIND! I figured it out. I knew it was going to be so simple I didn't
see it. The [Forms]![Employee Level Form]![Level] should have read
[Forms]![Employee Level Form]![cboLevel]

Chris said:
I have a combo box form created where the user can select among several
options (employee levels or titles).

I have a table of records with a checkbox/yes/no column on whether or not it
applies to a certain employee level.

I want a query that will show all records that are checked and that apply to
that employee level.

So, If the user selects lets say "manager" from the combo box form, the
query will show all records in which that column "Applies to Manager" is
checked as a yes. If they pick a different employee level, the manager data
should not appear.

Here's the criteria I currently have in the query:
IIf([Forms]![Employee Level Form]![Level]="Manager",True)

in SQL:

SELECT Chart.[Applies to Managers], Chart.[Manager Training Level]
FROM Chart
WHERE (((Chart.[Applies to Managers])=IIf([Forms]![Employee Level
Form]![Level]="Manager",True)));

Any help is appreciated
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top