Using IIF function in combination with Or

[Guest]

Hello,

does anyone know whther or not some variation of this statement is possible
Here is the example, I have a table with one field. the field contains
unique numbers as data. I want to write a query that spits out back the
contents of the table and an additional field that is created in the macro.
That field should be a bunch of IF statements, which in the end will assign a
unique label to each number.

In query, can use a bunch off IFFs somehow to get that result?

Field2: IIf([Table1].[Field1]="1","AA") Or IIf([Table1].[Field1]="2","BA")

I guess it's an attempt to write an if then statement without running it
through code.

Thanks,
pepenacho

 

[Douglas J. Steele]

You can nest Iif statements (although your syntax for them is wrong. An IIf
statement has 3 parts: the boolean comparison, the answer if the boolean is
True and the answer if it's false):

IIf([Table1].[Field1]="1","AA", IIf([Table1].[Field1]="2","BA", "???"))

There's also a Choose function, whose syntax is Choose(index, choice-1[,
choice-2, ... [, choice-n]]):

Choose(CInt([Table1].[Field1]), "AA", "BA")

However, the most flexible approach would be to have a table that converts
from one value to the other, and join your existing table to that second
table.

 

[Guest]

Doug thanks for writing back.

I tested the IIF without the 3rd part and it works. Hence, I left it out. In
my query each statement is guaranteed at least one hit, thus part 3 is not
necessary.

IIf([Table1].[Field1]="1","AA", IIf([Table1].[Field1]="2","BA", "???"))

even if I were to include a 3rd part in the stmtn above, it does not work.
The comma between "AA" and IIF seems out of place.

What I'm trying to accomplish is something where each IIF stmnt is exclusive
of the other.

Would you mind writing another example?
pepenacho

You can nest Iif statements (although your syntax for them is wrong. An IIf
statement has 3 parts: the boolean comparison, the answer if the boolean is
True and the answer if it's false):

IIf([Table1].[Field1]="1","AA", IIf([Table1].[Field1]="2","BA", "???"))

There's also a Choose function, whose syntax is Choose(index, choice-1[,
choice-2, ... [, choice-n]]):

Choose(CInt([Table1].[Field1]), "AA", "BA")

However, the most flexible approach would be to have a table that converts
from one value to the other, and join your existing table to that second
table.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

does anyone know whther or not some variation of this statement is possible
Here is the example, I have a table with one field. the field contains
unique numbers as data. I want to write a query that spits out back the
contents of the table and an additional field that is created in the macro.
That field should be a bunch of IF statements, which in the end will assign a
unique label to each number.

In query, can use a bunch off IFFs somehow to get that result?

Field2: IIf([Table1].[Field1]="1","AA") Or IIf([Table1].[Field1]="2","BA")

I guess it's an attempt to write an if then statement without running it
through code.

Thanks,
pepenacho

 

[Douglas J. Steele]

Trust me: my example works. You're correct that you can get away without the
3rd argument if you're using the IIf statement in a query, but it is
officially incorrect.

If the value of Field1 is "1", it will return "AA". If the value of Field1
is not "1", it will evaluate the second IIf statement. If the value of
Field1 is "2", it will return "BA". If the value of Field1 is not 2, it will
return "???".


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug thanks for writing back.

I tested the IIF without the 3rd part and it works. Hence, I left it out. In
my query each statement is guaranteed at least one hit, thus part 3 is not
necessary.

IIf([Table1].[Field1]="1","AA", IIf([Table1].[Field1]="2","BA", "???"))

even if I were to include a 3rd part in the stmtn above, it does not work.
The comma between "AA" and IIF seems out of place.

What I'm trying to accomplish is something where each IIF stmnt is exclusive
of the other.

Would you mind writing another example?
pepenacho

You can nest Iif statements (although your syntax for them is wrong. An IIf
statement has 3 parts: the boolean comparison, the answer if the boolean is
True and the answer if it's false):

IIf([Table1].[Field1]="1","AA", IIf([Table1].[Field1]="2","BA", "???"))

There's also a Choose function, whose syntax is Choose(index, choice-1[,
choice-2, ... [, choice-n]]):

Choose(CInt([Table1].[Field1]), "AA", "BA")

However, the most flexible approach would be to have a table that converts
from one value to the other, and join your existing table to that second
table.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

does anyone know whther or not some variation of this statement is possible
Here is the example, I have a table with one field. the field contains
unique numbers as data. I want to write a query that spits out back the
contents of the table and an additional field that is created in the macro.
That field should be a bunch of IF statements, which in the end will assign a
unique label to each number.

In query, can use a bunch off IFFs somehow to get that result?

Field2: IIf([Table1].[Field1]="1","AA") Or IIf([Table1].[Field1]="2","BA")

I guess it's an attempt to write an if then statement without running it
through code.

Thanks,
pepenacho