Iif coding seemingly not working

[Guest]

Alright, I have a table for each of my employees. We test for ten separate
second languages, and I have a field for each of these languages, containing
a lookup wizard to give a drop down menu of each possible test result. Now,
I'm trying to build a query that will give me a list of each employee who has
gotten an adequate score on each language test.

[FR] and [FRC] are the two French language tests (Parisien vs. Quebecois
French)

French: IIf(([FR] Or [FRC])=("5 - High Intermediate" Or "6 - High
Intermediate +" Or "7 - Advanced" Or "8 - Advanced +" Or "9 - Educated
Professional"),Yes,No)
Criteria is Yes

Except my query is still spitting out every record that has anything at all
entered in the FR or FRC fields, such as "Test Required" and "1 - Novice" etc.

I cannot for the life of me figure out why this is happening.

 

[David F Cox]

OR does not work like that. It works on logical True results. You should be
working on statements like [FR] = ["5 - High Intermediate"] which resolves
to TRUE or FALSE.
Your IIF statements get much longer:-
IIF ( [FR] = ["5 - High Intermediate"] OR [FRC] = ["5 - High Intermediate"]
OR [FR] = "6 - High Intermediate +" OR ....

You could use IN(...) instead.
IIF ( [FR] IN("5 - High Intermediate", "6 - High Intermediate +" , "7 -
Advanced" ....) OR [FRC] IN (.....

or maybe, if all of your data starts with a suitable number:

IIF( (VAL[FR] BETWEEN 6 AND 9) OR VAL{[FRC] BETWEEN 6 and 9, .......

 

[David F Cox]

oops, ( not {, (finger trouble)

IIF( (VAL[FR] BETWEEN 6 AND 9) OR VAL([FRC] BETWEEN 6 and 9, .......

and probably IIF(VAL[FR] >=6 OR VAL([FRC] >=6, ....

will do what you want

OR does not work like that. It works on logical True results. You should
be working on statements like [FR] = ["5 - High Intermediate"] which
resolves to TRUE or FALSE.
Your IIF statements get much longer:-
IIF ( [FR] = ["5 - High Intermediate"] OR [FRC] = ["5 - High
Intermediate"] OR [FR] = "6 - High Intermediate +" OR ....

You could use IN(...) instead.
IIF ( [FR] IN("5 - High Intermediate", "6 - High Intermediate +" , "7 -
Advanced" ....) OR [FRC] IN (.....

or maybe, if all of your data starts with a suitable number:

IIF( (VAL[FR] BETWEEN 6 AND 9) OR VAL{[FRC] BETWEEN 6 and 9, .......

Alright, I have a table for each of my employees. We test for ten
separate
second languages, and I have a field for each of these languages,
containing
a lookup wizard to give a drop down menu of each possible test result.
Now,
I'm trying to build a query that will give me a list of each employee who
has
gotten an adequate score on each language test.

[FR] and [FRC] are the two French language tests (Parisien vs. Quebecois
French)

French: IIf(([FR] Or [FRC])=("5 - High Intermediate" Or "6 - High
Intermediate +" Or "7 - Advanced" Or "8 - Advanced +" Or "9 - Educated
Professional"),Yes,No)
Criteria is Yes

Except my query is still spitting out every record that has anything at
all
entered in the FR or FRC fields, such as "Test Required" and "1 - Novice"
etc.

I cannot for the life of me figure out why this is happening.

 

[Guest]

Unfortunately, the values are text strings and not numbers - makes coding a
lot more difficult, but it makes the usability much easier.

I had success with
French: IIf((([FR]="5 - High Intermediate") Or ([FR]="6 - High Intermediate
+") Or ([FR]="7 - Advanced") Or ([FR]="8 - Advanced +") Or ([FR]="9 -
Educated Professional") Or ([FRC]="5 - High Intermediate") Or ([FRC]="6 -
High Intermediate +") Or ([FRC]="7 - Advanced") Or ([FRC]="8 - Advanced +")
Or ([FRC]="9 - Educated Professional")),Yes,No)

Thanks for the help!
--
-Ary

oops, ( not {, (finger trouble)

IIF( (VAL[FR] BETWEEN 6 AND 9) OR VAL([FRC] BETWEEN 6 and 9, .......

and probably IIF(VAL[FR] >=6 OR VAL([FRC] >=6, ....

will do what you want

OR does not work like that. It works on logical True results. You should
be working on statements like [FR] = ["5 - High Intermediate"] which
resolves to TRUE or FALSE.
Your IIF statements get much longer:-
IIF ( [FR] = ["5 - High Intermediate"] OR [FRC] = ["5 - High
Intermediate"] OR [FR] = "6 - High Intermediate +" OR ....

You could use IN(...) instead.
IIF ( [FR] IN("5 - High Intermediate", "6 - High Intermediate +" , "7 -
Advanced" ....) OR [FRC] IN (.....

or maybe, if all of your data starts with a suitable number:

IIF( (VAL[FR] BETWEEN 6 AND 9) OR VAL{[FRC] BETWEEN 6 and 9, .......

Alright, I have a table for each of my employees. We test for ten
separate
second languages, and I have a field for each of these languages,
containing
a lookup wizard to give a drop down menu of each possible test result.
Now,
I'm trying to build a query that will give me a list of each employee who
has
gotten an adequate score on each language test.

[FR] and [FRC] are the two French language tests (Parisien vs. Quebecois
French)

French: IIf(([FR] Or [FRC])=("5 - High Intermediate" Or "6 - High
Intermediate +" Or "7 - Advanced" Or "8 - Advanced +" Or "9 - Educated
Professional"),Yes,No)
Criteria is Yes

Except my query is still spitting out every record that has anything at
all
entered in the FR or FRC fields, such as "Test Required" and "1 - Novice"
etc.

I cannot for the life of me figure out why this is happening.

 

[David F Cox]

I realised that the codes were text strings. VAL() tries to convert a text
string to a value, starting at the beginning, and giving up when it runs
into invalid characters. So "9 Blah Blah Blah" gets converted to 9, and I
suspect that behaviour will do the job for you.

Unfortunately, the values are text strings and not numbers - makes coding
a
lot more difficult, but it makes the usability much easier.

I had success with
French: IIf((([FR]="5 - High Intermediate") Or ([FR]="6 - High
Intermediate
+") Or ([FR]="7 - Advanced") Or ([FR]="8 - Advanced +") Or ([FR]="9 -
Educated Professional") Or ([FRC]="5 - High Intermediate") Or ([FRC]="6 -
High Intermediate +") Or ([FRC]="7 - Advanced") Or ([FRC]="8 - Advanced
+")
Or ([FRC]="9 - Educated Professional")),Yes,No)

Thanks for the help!
--
-Ary

oops, ( not {, (finger trouble)

IIF( (VAL[FR] BETWEEN 6 AND 9) OR VAL([FRC] BETWEEN 6 and 9, .......

and probably IIF(VAL[FR] >=6 OR VAL([FRC] >=6, ....

will do what you want

OR does not work like that. It works on logical True results. You
should
be working on statements like [FR] = ["5 - High Intermediate"] which
resolves to TRUE or FALSE.
Your IIF statements get much longer:-
IIF ( [FR] = ["5 - High Intermediate"] OR [FRC] = ["5 - High
Intermediate"] OR [FR] = "6 - High Intermediate +" OR ....

You could use IN(...) instead.
IIF ( [FR] IN("5 - High Intermediate", "6 - High Intermediate +" ,
"7 -
Advanced" ....) OR [FRC] IN (.....

or maybe, if all of your data starts with a suitable number:

IIF( (VAL[FR] BETWEEN 6 AND 9) OR VAL{[FRC] BETWEEN 6 and 9, .......



Alright, I have a table for each of my employees. We test for ten
separate
second languages, and I have a field for each of these languages,
containing
a lookup wizard to give a drop down menu of each possible test result.
Now,
I'm trying to build a query that will give me a list of each employee
who
has
gotten an adequate score on each language test.

[FR] and [FRC] are the two French language tests (Parisien vs.
Quebecois
French)

French: IIf(([FR] Or [FRC])=("5 - High Intermediate" Or "6 - High
Intermediate +" Or "7 - Advanced" Or "8 - Advanced +" Or "9 - Educated
Professional"),Yes,No)
Criteria is Yes

Except my query is still spitting out every record that has anything
at
all
entered in the FR or FRC fields, such as "Test Required" and "1 -
Novice"
etc.

I cannot for the life of me figure out why this is happening.