IIF stmt in query

  • Thread starter Thread starter AMZ
  • Start date Start date
A

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
 
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
 
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!


vanderghast said:
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 said:
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
Click to expand...
Click to expand...
 
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
Click to expand...

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).
 
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



AMZ said:
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!


vanderghast said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...

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).
Click to expand...
 
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

John W. Vinson said:
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
Click to expand...

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).
Click to expand...
 
Back
Top