If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IIF stmt in query
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 |
#2
|
|||
|
|||
IIF stmt in query
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" wrote in message ... 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 |
#3
|
|||
|
|||
IIF stmt in query
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" wrote: 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" wrote in message ... 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 |
#4
|
|||
|
|||
IIF stmt in query
On Mon, 1 Mar 2010 14:41:01 -0800, AMZ wrote:
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 [MVP] |
#5
|
|||
|
|||
IIF stmt in query
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#6
|
|||
|
|||
IIF stmt in query
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 John W. Vinson wrote: On Mon, 1 Mar 2010 14:41:01 -0800, AMZ wrote: 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). |
#7
|
|||
|
|||
IIF stmt in query
On Tue, 02 Mar 2010 09:04:01 -0500, John Spencer wrote:
Why would you nest two switch conditions? It seems to me that one would do, D'oh! Quite right, John. Brainfade. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
IIF stmt in query
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" wrote: On Mon, 1 Mar 2010 14:41:01 -0800, AMZ wrote: 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 [MVP] . |
Thread Tools | |
Display Modes | |
|
|