IIF stmt in query

[AMZ]

I would like to return different results for different conditions more than
one field. here is my IIF stmt:

IIF([transactiontbl.insttype]="A",â€FMYHF0002â€,IIF([transactiontbl.insttype]="B","FMYHF0003â€,IIF([transactiontbl.insttype]="T",â€FMYHF0005â€,0)))
or IIF([etype]=â€Râ€,â€FMYHF0004â€,â€FMYHF0006â€)

any help is greatly appreciated. Thanks

 

[vanderghast]

You can AND the conditions, but it would be preferable to use a table to
make the translations.

iif( type = "A" AND country = "USA", "FMYHF002",
iif( type = "A" AND country = "CAN" , "FMYHF002C",
...


or


iif( type="A", iif( country = "USA", "FMYHF002",
iif( country= "CAN", "FMYHF002X" ,
...
iif( type = "B", ...


as you see, it is quite hard to read, and to maintain. With a table:

Translations ' table name
Type Country Translation ' fields
"A" "USA" "FMYHF002"
"A" "CAN" "FMYHF002X"
"B" ... ... ' data


then, the query will be:



----------------
SELECT ..., translations.translation
FROM myTable INNER JOIN translations
ON myTable.Country= translations.Country
AND myTable.Type = translations.Type
----------------


much simpler, since the data is where it should be, in a table.



Vanderghast, Access MVP

 

[AMZ]

Thanks, but i'm not sure i was clear in my question. I have one field
(TransType) that has the following data A, B, T, E and another field (EType)
that goes with the E in the TransType that contains R or U. Here is what i
want to return for each option:

For A return FMYHF0002
For B return FMYHF0003
For T return FMYHF0005
For E with an etype of R return FMYHF0004
For E with an etype of U return FMYHF0006

Thank you for your quick response!

You can AND the conditions, but it would be preferable to use a table to
make the translations.

iif( type = "A" AND country = "USA", "FMYHF002",
iif( type = "A" AND country = "CAN" , "FMYHF002C",
...


or


iif( type="A", iif( country = "USA", "FMYHF002",
iif( country= "CAN", "FMYHF002X" ,
...
iif( type = "B", ...


as you see, it is quite hard to read, and to maintain. With a table:

Translations ' table name
Type Country Translation ' fields
"A" "USA" "FMYHF002"
"A" "CAN" "FMYHF002X"
"B" ... ... ' data


then, the query will be:



----------------
SELECT ..., translations.translation
FROM myTable INNER JOIN translations
ON myTable.Country= translations.Country
AND myTable.Type = translations.Type
----------------


much simpler, since the data is where it should be, in a table.



Vanderghast, Access MVP

I would like to return different results for different conditions more than
one field. here is my IIF stmt:

IIF([transactiontbl.insttype]="A",â€FMYHF0002â€,IIF([transactiontbl.insttype]="B","FMYHF0003â€,IIF([transactiontbl.insttype]="T",â€FMYHF0005â€,0)))
or IIF([etype]=â€Râ€,â€FMYHF0004â€,â€FMYHF0006â€)

any help is greatly appreciated. Thanks

 

[John W. Vinson]

Thanks, but i'm not sure i was clear in my question. I have one field
(TransType) that has the following data A, B, T, E and another field (EType)
that goes with the E in the TransType that contains R or U. Here is what i
want to return for each option:

For A return FMYHF0002
For B return FMYHF0003
For T return FMYHF0005
For E with an etype of R return FMYHF0004
For E with an etype of U return FMYHF0006

I'd suggest using two calls to the Switch function - sort of a multibranch
IIF:

Switch([TransType] = "A", "FMYHF0002", [TransType] = "B", "FMYHF0003",
[TransType] = "T", "FMYHF0005", [TransType] = "E", Switch([EType] = "R",
"FMYHF0004", [EType] = "U", "FMYHF0006", True, "Error in input"), True, "Error
in input")

The Switch function takes arguments in pairs, and evaluates them left to
right; when it first finds a pair for which the first element is TRUE it
returns the second element. Therefore I added a "catch" condition last - if it
gets through all the pairs and NONE of them match, it will return "Error in
Input" (which you can change to a more useful message or to NULL if you
prefer).

 

[vanderghast]

See John's solution for using iif like construction. For a table approach:

Translations ' table name
TransType EType Translation ' fields
A * FMYHF0002
B * FMYHF0003
T * FMYHF0005
E R FMYHF0004
E U FMYHF0006 'data


and use an SQL statement like

------------------
SELECT mytable.*, translations.translation
FROM myTable INNER JOIN translations
ON myTable.TransType = translations.TransType
AND myTable.EType LIKE translations.EType
-------------------


and again, the data can be modified in the table, rather than in the code,
as, for example, adding a new required 'code' : your end user simply add one
record in the table Translations instead of modifying the SQL iif statement.


Vanderghast, Access MVP

Thanks, but i'm not sure i was clear in my question. I have one field
(TransType) that has the following data A, B, T, E and another field
(EType)
that goes with the E in the TransType that contains R or U. Here is what
i
want to return for each option:

For A return FMYHF0002
For B return FMYHF0003
For T return FMYHF0005
For E with an etype of R return FMYHF0004
For E with an etype of U return FMYHF0006

Thank you for your quick response!

You can AND the conditions, but it would be preferable to use a table to
make the translations.

iif( type = "A" AND country = "USA", "FMYHF002",
iif( type = "A" AND country = "CAN" , "FMYHF002C",
...


or


iif( type="A", iif( country = "USA", "FMYHF002",
iif( country= "CAN", "FMYHF002X" ,
...
iif( type = "B", ...


as you see, it is quite hard to read, and to maintain. With a table:

Translations ' table name
Type Country Translation ' fields
"A" "USA" "FMYHF002"
"A" "CAN" "FMYHF002X"
"B" ... ... ' data


then, the query will be:



----------------
SELECT ..., translations.translation
FROM myTable INNER JOIN translations
ON myTable.Country= translations.Country
AND myTable.Type = translations.Type
----------------


much simpler, since the data is where it should be, in a table.



Vanderghast, Access MVP

I would like to return different results for different conditions more
than
one field. here is my IIF stmt:

IIF([transactiontbl.insttype]="A",â€FMYHF0002â€,IIF([transactiontbl.insttype]="B","FMYHF0003â€,IIF([transactiontbl.insttype]="T",â€FMYHF0005â€,0)))
or IIF([etype]=â€Râ€,â€FMYHF0004â€,â€FMYHF0006â€)

any help is greatly appreciated. Thanks

 

[John Spencer]

John,

Why would you nest two switch conditions? It seems to me that one would do,

Switch(
[TransType] = "A", "FMYHF0002",
[TransType] = "B", "FMYHF0003",
[TransType] = "T", "FMYHF0005",
[TransType] = "E" AND [EType] = "R","FMYHF0004",
[TransType] = "E" and [EType] = "U", "FMYHF0006",
, True, "Error in input")

AMZ,
You could still do this with a table
Result: Your FMY Codes
TransType: A, B, T, E
EType: "*" for A, B, and T and R or U for EType

THen you could use that in a Non-equi join to get the results - to use
Vanderghast example, the query would be changed to:

SELECT ..., translations.translation
FROM myTable INNER JOIN translations
ON myTable.Country = translations.Country
AND myTable.Type LIKE translations.Type

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thanks, but i'm not sure i was clear in my question. I have one field
(TransType) that has the following data A, B, T, E and another field (EType)
that goes with the E in the TransType that contains R or U. Here is what i
want to return for each option:

For A return FMYHF0002
For B return FMYHF0003
For T return FMYHF0005
For E with an etype of R return FMYHF0004
For E with an etype of U return FMYHF0006

I'd suggest using two calls to the Switch function - sort of a multibranch
IIF:

Switch([TransType] = "A", "FMYHF0002", [TransType] = "B", "FMYHF0003",
[TransType] = "T", "FMYHF0005", [TransType] = "E", Switch([EType] = "R",
"FMYHF0004", [EType] = "U", "FMYHF0006", True, "Error in input"), True, "Error
in input")

The Switch function takes arguments in pairs, and evaluates them left to
right; when it first finds a pair for which the first element is TRUE it
returns the second element. Therefore I added a "catch" condition last - if it
gets through all the pairs and NONE of them match, it will return "Error in
Input" (which you can change to a more useful message or to NULL if you
prefer).

 

[John W. Vinson]

Why would you nest two switch conditions? It seems to me that one would do,

D'oh!

Quite right, John. Brainfade.

 

[AMZ]

John: thanks for the switch statement. It now works! I was not familar with
this type of function so thanks so much for sharing it with me. Thanks too
to all the other posts. Very helpful!

amz

Thanks, but i'm not sure i was clear in my question. I have one field
(TransType) that has the following data A, B, T, E and another field (EType)
that goes with the E in the TransType that contains R or U. Here is what i
want to return for each option:

For A return FMYHF0002
For B return FMYHF0003
For T return FMYHF0005
For E with an etype of R return FMYHF0004
For E with an etype of U return FMYHF0006

I'd suggest using two calls to the Switch function - sort of a multibranch
IIF:

Switch([TransType] = "A", "FMYHF0002", [TransType] = "B", "FMYHF0003",
[TransType] = "T", "FMYHF0005", [TransType] = "E", Switch([EType] = "R",
"FMYHF0004", [EType] = "U", "FMYHF0006", True, "Error in input"), True, "Error
in input")

The Switch function takes arguments in pairs, and evaluates them left to
right; when it first finds a pair for which the first element is TRUE it
returns the second element. Therefore I added a "catch" condition last - if it
gets through all the pairs and NONE of them match, it will return "Error in
Input" (which you can change to a more useful message or to NULL if you
prefer).