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
|
|||
|
|||
how do I avoid "expression too complex" error with nested IIf?
I have about 20 parameters and can't seem to fit them all in one nested iif
expression. Is there a way to create an expression that would cycle through those twenty (field) parameters? |
#2
|
|||
|
|||
how do I avoid "expression too complex" error with nested IIf?
Need a few more details if we are going to give you any meaningful help.
What are the tables and fields in your query, what does your IIF( ) statement look like now? What are you actually trying to accomplish? Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I have about 20 parameters and can't seem to fit them all in one nested iif expression. Is there a way to create an expression that would cycle through those twenty (field) parameters? |
#3
|
|||
|
|||
how do I avoid "expression too complex" error with nested IIf?
There's a very good chance that your table isn't properly normalized. If the
data in one field depends on data in another, that's the basic problem. An example of a problem would be something like below in that what's in one field changes the value for another field. IIf([Field1] = "Blue", [Field1], IIf([Field2] = "Blue", "Red", II([Field3] = "Red", "Blue", etc.... -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Yamou" wrote: I have about 20 parameters and can't seem to fit them all in one nested iif expression. Is there a way to create an expression that would cycle through those twenty (field) parameters? |
#4
|
|||
|
|||
how do I avoid "expression too complex" error with nested IIf?
If you are trying to nest a bunch of IIF statements, you are only allowed to
go 7 deep. Beyond that I can't picture what you are trying to do. You could write a custom function and pass it up to 39 arguments and perhaps have it do what you want. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Yamou" wrote in message ... I have about 20 parameters and can't seem to fit them all in one nested iif expression. Is there a way to create an expression that would cycle through those twenty (field) parameters? |
#5
|
|||
|
|||
how do I avoid "expression too complex" error with nested IIf?
I am using an update query with a single table comprised of text, date and
number fields (the fields to query are all number fields and the destination field [TRANS_CATEGORY]). Now these are transactions that get posted first in a field called [TRANS_AMOUNT] and then to their respective category - one of the 18 fields in question (education, health, office expense, transp, etc.) Purpose/idea: to update the [TRANS_CATEGORY] field with the right label depending on whether a record was posted in that category (i.e. the single non null record in a row where an amount is posted). I use the following IIF construction: UPDATE REPIMPORT SET REPIMPORT.TRANS_CATEGORY = IIf([SAL_BEN] Like "*","Salaries & Benefits",IIf([TUITION] Like "*","Tuition",IIf([SUPPL_OTHER] Like "*","Supplies & Other",IIf([HEALTH] Like "*","Health",IIf([NUTRITION] Like "*","Nutrition",IIf([HEALTH] Like "*","Health",IIf([CLOTHING] Like "*","Clothing",IIf([RECREATION] Like "*","Recreation",IIf([CHRIS_FORM] Like "*","Christian Formation",IIf([HELP_TO_FAM] Like "*","Help to Families",IIf([SPEC_ GIFTS] Like "*","Special Gifts",IIf([OTHER_BEN] Like "*","Other Benefits",IIf([BEN_EXPL] Like "*","Benefits Explanation",IIf([SALARIES] Like "*","Salaries",IIf([EMPLOY_BEN] Like "*","Employee Benefits",IIf([PHOTOS] Like "*","Photos",IIf([OFFICE_EXP] Like "*","Office Expense"IIf([TRANSP] Like "*","Transportation",IIf([OTHER_EXP] Like "*","Other Expenses","" ))))))))))))))))))); If I keep all the above fields in the expression, I get the message: "the expression you entered is too complex". If, however, I truncate the expression down to certain length, say up to [OTHER_BEN], then It returns the right label (Salaries and Benefits to Other Benefits) in [TRANS_CATEGORY], and nothing in all others (if I specify "" in the last If statement). I want to go beyond this limitation from the first category (salaries & Benefits) all the way to the last category (Other expenses). Maybe the if Function like in excel has a n number of nesting possible or should I take another approach to be able include all my parameters in the query? Please advise and thanks a lot! "Dale Fye" wrote: Need a few more details if we are going to give you any meaningful help. What are the tables and fields in your query, what does your IIF( ) statement look like now? What are you actually trying to accomplish? Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I have about 20 parameters and can't seem to fit them all in one nested iif expression. Is there a way to create an expression that would cycle through those twenty (field) parameters? |
#6
|
|||
|
|||
how do I avoid "expression too complex" error with nested IIf?
I think what you need to do to start with is create a "normalizing" query.
It would look something like: SELECT ID, "Salaries & Benefits" as Trans_Category FROM yourTable WHERE NOT ISNULL([SAL_BEN]) UNION ALL SELECT ID, "Tuition" as Trans_Category FROM yourTable WHERE NOT ISNULL([TUITION]) UNION ALL SELECT ID, "Supplies & Other" as Trans_Category FROM yourTable WHERE NOT ISNULL([SUPPL_OTHER]) .... (for all of your 18 fields). Once you have done that, you should be able to join that to your other table and update the [Trans_Category] field. If that doesn't work, you could do a DLOOKUP( ) pointed at this new "normalization query" for each record UPDATE yourTable SET [TRANS_CATEGORY] = DLOOKUP("Trans_Category", "Normalization_Query", "ID = " & ID) HTH Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I am using an update query with a single table comprised of text, date and number fields (the fields to query are all number fields and the destination field [TRANS_CATEGORY]). Now these are transactions that get posted first in a field called [TRANS_AMOUNT] and then to their respective category - one of the 18 fields in question (education, health, office expense, transp, etc.) Purpose/idea: to update the [TRANS_CATEGORY] field with the right label depending on whether a record was posted in that category (i.e. the single non null record in a row where an amount is posted). I use the following IIF construction: UPDATE REPIMPORT SET REPIMPORT.TRANS_CATEGORY = IIf([SAL_BEN] Like "*","Salaries & Benefits",IIf([TUITION] Like "*","Tuition",IIf([SUPPL_OTHER] Like "*","Supplies & Other",IIf([HEALTH] Like "*","Health",IIf([NUTRITION] Like "*","Nutrition",IIf([HEALTH] Like "*","Health",IIf([CLOTHING] Like "*","Clothing",IIf([RECREATION] Like "*","Recreation",IIf([CHRIS_FORM] Like "*","Christian Formation",IIf([HELP_TO_FAM] Like "*","Help to Families",IIf([SPEC_ GIFTS] Like "*","Special Gifts",IIf([OTHER_BEN] Like "*","Other Benefits",IIf([BEN_EXPL] Like "*","Benefits Explanation",IIf([SALARIES] Like "*","Salaries",IIf([EMPLOY_BEN] Like "*","Employee Benefits",IIf([PHOTOS] Like "*","Photos",IIf([OFFICE_EXP] Like "*","Office Expense"IIf([TRANSP] Like "*","Transportation",IIf([OTHER_EXP] Like "*","Other Expenses","" ))))))))))))))))))); If I keep all the above fields in the expression, I get the message: "the expression you entered is too complex". If, however, I truncate the expression down to certain length, say up to [OTHER_BEN], then It returns the right label (Salaries and Benefits to Other Benefits) in [TRANS_CATEGORY], and nothing in all others (if I specify "" in the last If statement). I want to go beyond this limitation from the first category (salaries & Benefits) all the way to the last category (Other expenses). Maybe the if Function like in excel has a n number of nesting possible or should I take another approach to be able include all my parameters in the query? Please advise and thanks a lot! "Dale Fye" wrote: Need a few more details if we are going to give you any meaningful help. What are the tables and fields in your query, what does your IIF( ) statement look like now? What are you actually trying to accomplish? Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I have about 20 parameters and can't seem to fit them all in one nested iif expression. Is there a way to create an expression that would cycle through those twenty (field) parameters? |
#7
|
|||
|
|||
how do I avoid "expression too complex" error with nested IIf?
I have tried that, but having no experience with "normalizing" queries i get
a parameter popup asking for a value for ID, what should the ID field match? I suppose some sort of unique record (key) field. Also, when I use this expression, the query appears as a passthrough query; i suppose that should be the case. "Dale Fye" wrote: I think what you need to do to start with is create a "normalizing" query. It would look something like: SELECT ID, "Salaries & Benefits" as Trans_Category FROM yourTable WHERE NOT ISNULL([SAL_BEN]) UNION ALL SELECT ID, "Tuition" as Trans_Category FROM yourTable WHERE NOT ISNULL([TUITION]) UNION ALL SELECT ID, "Supplies & Other" as Trans_Category FROM yourTable WHERE NOT ISNULL([SUPPL_OTHER]) ... (for all of your 18 fields). Once you have done that, you should be able to join that to your other table and update the [Trans_Category] field. If that doesn't work, you could do a DLOOKUP( ) pointed at this new "normalization query" for each record UPDATE yourTable SET [TRANS_CATEGORY] = DLOOKUP("Trans_Category", "Normalization_Query", "ID = " & ID) HTH Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I am using an update query with a single table comprised of text, date and number fields (the fields to query are all number fields and the destination field [TRANS_CATEGORY]). Now these are transactions that get posted first in a field called [TRANS_AMOUNT] and then to their respective category - one of the 18 fields in question (education, health, office expense, transp, etc.) Purpose/idea: to update the [TRANS_CATEGORY] field with the right label depending on whether a record was posted in that category (i.e. the single non null record in a row where an amount is posted). I use the following IIF construction: UPDATE REPIMPORT SET REPIMPORT.TRANS_CATEGORY = IIf([SAL_BEN] Like "*","Salaries & Benefits",IIf([TUITION] Like "*","Tuition",IIf([SUPPL_OTHER] Like "*","Supplies & Other",IIf([HEALTH] Like "*","Health",IIf([NUTRITION] Like "*","Nutrition",IIf([HEALTH] Like "*","Health",IIf([CLOTHING] Like "*","Clothing",IIf([RECREATION] Like "*","Recreation",IIf([CHRIS_FORM] Like "*","Christian Formation",IIf([HELP_TO_FAM] Like "*","Help to Families",IIf([SPEC_ GIFTS] Like "*","Special Gifts",IIf([OTHER_BEN] Like "*","Other Benefits",IIf([BEN_EXPL] Like "*","Benefits Explanation",IIf([SALARIES] Like "*","Salaries",IIf([EMPLOY_BEN] Like "*","Employee Benefits",IIf([PHOTOS] Like "*","Photos",IIf([OFFICE_EXP] Like "*","Office Expense"IIf([TRANSP] Like "*","Transportation",IIf([OTHER_EXP] Like "*","Other Expenses","" ))))))))))))))))))); If I keep all the above fields in the expression, I get the message: "the expression you entered is too complex". If, however, I truncate the expression down to certain length, say up to [OTHER_BEN], then It returns the right label (Salaries and Benefits to Other Benefits) in [TRANS_CATEGORY], and nothing in all others (if I specify "" in the last If statement). I want to go beyond this limitation from the first category (salaries & Benefits) all the way to the last category (Other expenses). Maybe the if Function like in excel has a n number of nesting possible or should I take another approach to be able include all my parameters in the query? Please advise and thanks a lot! "Dale Fye" wrote: Need a few more details if we are going to give you any meaningful help. What are the tables and fields in your query, what does your IIF( ) statement look like now? What are you actually trying to accomplish? Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I have about 20 parameters and can't seem to fit them all in one nested iif expression. Is there a way to create an expression that would cycle through those twenty (field) parameters? |
#8
|
|||
|
|||
how do I avoid "expression too complex" error with nested IIf?
The ID field I put in my query should be replaced by the name of the primary
key field in your table. If your primary key contains more than one field, then you should list all of the fields that are in your primary key. When you save this query the query should not be saved as a "pass through" query. The image to the left of the query should actually look like two interlocking rings, sort of like the Olympic rings, but with only two. "Yamou" wrote in message ... I have tried that, but having no experience with "normalizing" queries i get a parameter popup asking for a value for ID, what should the ID field match? I suppose some sort of unique record (key) field. Also, when I use this expression, the query appears as a passthrough query; i suppose that should be the case. "Dale Fye" wrote: I think what you need to do to start with is create a "normalizing" query. It would look something like: SELECT ID, "Salaries & Benefits" as Trans_Category FROM yourTable WHERE NOT ISNULL([SAL_BEN]) UNION ALL SELECT ID, "Tuition" as Trans_Category FROM yourTable WHERE NOT ISNULL([TUITION]) UNION ALL SELECT ID, "Supplies & Other" as Trans_Category FROM yourTable WHERE NOT ISNULL([SUPPL_OTHER]) ... (for all of your 18 fields). Once you have done that, you should be able to join that to your other table and update the [Trans_Category] field. If that doesn't work, you could do a DLOOKUP( ) pointed at this new "normalization query" for each record UPDATE yourTable SET [TRANS_CATEGORY] = DLOOKUP("Trans_Category", "Normalization_Query", "ID = " & ID) HTH Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I am using an update query with a single table comprised of text, date and number fields (the fields to query are all number fields and the destination field [TRANS_CATEGORY]). Now these are transactions that get posted first in a field called [TRANS_AMOUNT] and then to their respective category - one of the 18 fields in question (education, health, office expense, transp, etc.) Purpose/idea: to update the [TRANS_CATEGORY] field with the right label depending on whether a record was posted in that category (i.e. the single non null record in a row where an amount is posted). I use the following IIF construction: UPDATE REPIMPORT SET REPIMPORT.TRANS_CATEGORY = IIf([SAL_BEN] Like "*","Salaries & Benefits",IIf([TUITION] Like "*","Tuition",IIf([SUPPL_OTHER] Like "*","Supplies & Other",IIf([HEALTH] Like "*","Health",IIf([NUTRITION] Like "*","Nutrition",IIf([HEALTH] Like "*","Health",IIf([CLOTHING] Like "*","Clothing",IIf([RECREATION] Like "*","Recreation",IIf([CHRIS_FORM] Like "*","Christian Formation",IIf([HELP_TO_FAM] Like "*","Help to Families",IIf([SPEC_ GIFTS] Like "*","Special Gifts",IIf([OTHER_BEN] Like "*","Other Benefits",IIf([BEN_EXPL] Like "*","Benefits Explanation",IIf([SALARIES] Like "*","Salaries",IIf([EMPLOY_BEN] Like "*","Employee Benefits",IIf([PHOTOS] Like "*","Photos",IIf([OFFICE_EXP] Like "*","Office Expense"IIf([TRANSP] Like "*","Transportation",IIf([OTHER_EXP] Like "*","Other Expenses","" ))))))))))))))))))); If I keep all the above fields in the expression, I get the message: "the expression you entered is too complex". If, however, I truncate the expression down to certain length, say up to [OTHER_BEN], then It returns the right label (Salaries and Benefits to Other Benefits) in [TRANS_CATEGORY], and nothing in all others (if I specify "" in the last If statement). I want to go beyond this limitation from the first category (salaries & Benefits) all the way to the last category (Other expenses). Maybe the if Function like in excel has a n number of nesting possible or should I take another approach to be able include all my parameters in the query? Please advise and thanks a lot! "Dale Fye" wrote: Need a few more details if we are going to give you any meaningful help. What are the tables and fields in your query, what does your IIF( ) statement look like now? What are you actually trying to accomplish? Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I have about 20 parameters and can't seem to fit them all in one nested iif expression. Is there a way to create an expression that would cycle through those twenty (field) parameters? |
#9
|
|||
|
|||
how do I avoid "expression too complex" error with nested IIf?
Thanks Dale,
I am almost done. It works with and I get the the TCATEGORY Field Updated with the right data. Now I am not sure how to join the tow tables; I have not done of these in quite a while. At this point I have Normalization query results with the following fields: PNAME, SPNAME,TDATE,PAYEE,REF,EXP,BBAL,CBAL,TCATEGORY The table to which I want to join the normalization query has the exact same fields and more, with the TCATEGORY field to update I tried the Update query you suggested, but because I have more than one key field (8), I didn't know how to write the last argument like "ID ="& ID in your example. Once I figure out how to transfer the data from the Normalization query to my REPIMPORT table, I will be done. Thanks for your patience "Dale Fye" wrote: The ID field I put in my query should be replaced by the name of the primary key field in your table. If your primary key contains more than one field, then you should list all of the fields that are in your primary key. When you save this query the query should not be saved as a "pass through" query. The image to the left of the query should actually look like two interlocking rings, sort of like the Olympic rings, but with only two. "Yamou" wrote in message ... I have tried that, but having no experience with "normalizing" queries i get a parameter popup asking for a value for ID, what should the ID field match? I suppose some sort of unique record (key) field. Also, when I use this expression, the query appears as a passthrough query; i suppose that should be the case. "Dale Fye" wrote: I think what you need to do to start with is create a "normalizing" query. It would look something like: SELECT ID, "Salaries & Benefits" as Trans_Category FROM yourTable WHERE NOT ISNULL([SAL_BEN]) UNION ALL SELECT ID, "Tuition" as Trans_Category FROM yourTable WHERE NOT ISNULL([TUITION]) UNION ALL SELECT ID, "Supplies & Other" as Trans_Category FROM yourTable WHERE NOT ISNULL([SUPPL_OTHER]) ... (for all of your 18 fields). Once you have done that, you should be able to join that to your other table and update the [Trans_Category] field. If that doesn't work, you could do a DLOOKUP( ) pointed at this new "normalization query" for each record UPDATE yourTable SET [TRANS_CATEGORY] = DLOOKUP("Trans_Category", "Normalization_Query", "ID = " & ID) HTH Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I am using an update query with a single table comprised of text, date and number fields (the fields to query are all number fields and the destination field [TRANS_CATEGORY]). Now these are transactions that get posted first in a field called [TRANS_AMOUNT] and then to their respective category - one of the 18 fields in question (education, health, office expense, transp, etc.) Purpose/idea: to update the [TRANS_CATEGORY] field with the right label depending on whether a record was posted in that category (i.e. the single non null record in a row where an amount is posted). I use the following IIF construction: UPDATE REPIMPORT SET REPIMPORT.TRANS_CATEGORY = IIf([SAL_BEN] Like "*","Salaries & Benefits",IIf([TUITION] Like "*","Tuition",IIf([SUPPL_OTHER] Like "*","Supplies & Other",IIf([HEALTH] Like "*","Health",IIf([NUTRITION] Like "*","Nutrition",IIf([HEALTH] Like "*","Health",IIf([CLOTHING] Like "*","Clothing",IIf([RECREATION] Like "*","Recreation",IIf([CHRIS_FORM] Like "*","Christian Formation",IIf([HELP_TO_FAM] Like "*","Help to Families",IIf([SPEC_ GIFTS] Like "*","Special Gifts",IIf([OTHER_BEN] Like "*","Other Benefits",IIf([BEN_EXPL] Like "*","Benefits Explanation",IIf([SALARIES] Like "*","Salaries",IIf([EMPLOY_BEN] Like "*","Employee Benefits",IIf([PHOTOS] Like "*","Photos",IIf([OFFICE_EXP] Like "*","Office Expense"IIf([TRANSP] Like "*","Transportation",IIf([OTHER_EXP] Like "*","Other Expenses","" ))))))))))))))))))); If I keep all the above fields in the expression, I get the message: "the expression you entered is too complex". If, however, I truncate the expression down to certain length, say up to [OTHER_BEN], then It returns the right label (Salaries and Benefits to Other Benefits) in [TRANS_CATEGORY], and nothing in all others (if I specify "" in the last If statement). I want to go beyond this limitation from the first category (salaries & Benefits) all the way to the last category (Other expenses). Maybe the if Function like in excel has a n number of nesting possible or should I take another approach to be able include all my parameters in the query? Please advise and thanks a lot! "Dale Fye" wrote: Need a few more details if we are going to give you any meaningful help. What are the tables and fields in your query, what does your IIF( ) statement look like now? What are you actually trying to accomplish? Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I have about 20 parameters and can't seem to fit them all in one nested iif expression. Is there a way to create an expression that would cycle through those twenty (field) parameters? |
#10
|
|||
|
|||
how do I avoid "expression too complex" error with nested IIf?
I don't know what all of those fields are, but my guess is that you don't
need all of them to define a unique record within your table. My quess is that you could get by with SPNAME, TDATE, PAYEE, and REF; but I'd have to look at your data to be sure. First, I would try writing a query that joins your original table to the normalized query. Add the table and the query grid to your query grid, join them on all of the fields that you listed in your note below, except the TRANS_CATEGORY field, add the TRANS_CATEGORY field from "yourTable" to the grid, change the query type to Update, and in the "update to" row, enter [queryname].[Trans_Category]. Try to run the query. If it works, great. If it doesn't, it will probably tell you the query is not updateable. In that case, you will need to use the DLOOKUP( ) function. If you do this, the normalization query will get run over and over again. What I would do is create another query that uses the normalization query and creates a temporary table. Once you have done this, you might want to try the previous step again (updating from a temp table may work where it didn't work when you tried to update from a query). If that doesn't work, then you will have to use the DLOOKUP function. It would be easier to show you what that would look like if you can provide the data types of each of the columns you listed below (or at least the key ones if done some more analysis based on my note above). Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: Thanks Dale, I am almost done. It works with and I get the the TCATEGORY Field Updated with the right data. Now I am not sure how to join the tow tables; I have not done of these in quite a while. At this point I have Normalization query results with the following fields: PNAME, SPNAME,TDATE,PAYEE,REF,EXP,BBAL,CBAL,TCATEGORY The table to which I want to join the normalization query has the exact same fields and more, with the TCATEGORY field to update I tried the Update query you suggested, but because I have more than one key field (8), I didn't know how to write the last argument like "ID ="& ID in your example. Once I figure out how to transfer the data from the Normalization query to my REPIMPORT table, I will be done. Thanks for your patience "Dale Fye" wrote: The ID field I put in my query should be replaced by the name of the primary key field in your table. If your primary key contains more than one field, then you should list all of the fields that are in your primary key. When you save this query the query should not be saved as a "pass through" query. The image to the left of the query should actually look like two interlocking rings, sort of like the Olympic rings, but with only two. "Yamou" wrote in message ... I have tried that, but having no experience with "normalizing" queries i get a parameter popup asking for a value for ID, what should the ID field match? I suppose some sort of unique record (key) field. Also, when I use this expression, the query appears as a passthrough query; i suppose that should be the case. "Dale Fye" wrote: I think what you need to do to start with is create a "normalizing" query. It would look something like: SELECT ID, "Salaries & Benefits" as Trans_Category FROM yourTable WHERE NOT ISNULL([SAL_BEN]) UNION ALL SELECT ID, "Tuition" as Trans_Category FROM yourTable WHERE NOT ISNULL([TUITION]) UNION ALL SELECT ID, "Supplies & Other" as Trans_Category FROM yourTable WHERE NOT ISNULL([SUPPL_OTHER]) ... (for all of your 18 fields). Once you have done that, you should be able to join that to your other table and update the [Trans_Category] field. If that doesn't work, you could do a DLOOKUP( ) pointed at this new "normalization query" for each record UPDATE yourTable SET [TRANS_CATEGORY] = DLOOKUP("Trans_Category", "Normalization_Query", "ID = " & ID) HTH Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I am using an update query with a single table comprised of text, date and number fields (the fields to query are all number fields and the destination field [TRANS_CATEGORY]). Now these are transactions that get posted first in a field called [TRANS_AMOUNT] and then to their respective category - one of the 18 fields in question (education, health, office expense, transp, etc.) Purpose/idea: to update the [TRANS_CATEGORY] field with the right label depending on whether a record was posted in that category (i.e. the single non null record in a row where an amount is posted). I use the following IIF construction: UPDATE REPIMPORT SET REPIMPORT.TRANS_CATEGORY = IIf([SAL_BEN] Like "*","Salaries & Benefits",IIf([TUITION] Like "*","Tuition",IIf([SUPPL_OTHER] Like "*","Supplies & Other",IIf([HEALTH] Like "*","Health",IIf([NUTRITION] Like "*","Nutrition",IIf([HEALTH] Like "*","Health",IIf([CLOTHING] Like "*","Clothing",IIf([RECREATION] Like "*","Recreation",IIf([CHRIS_FORM] Like "*","Christian Formation",IIf([HELP_TO_FAM] Like "*","Help to Families",IIf([SPEC_ GIFTS] Like "*","Special Gifts",IIf([OTHER_BEN] Like "*","Other Benefits",IIf([BEN_EXPL] Like "*","Benefits Explanation",IIf([SALARIES] Like "*","Salaries",IIf([EMPLOY_BEN] Like "*","Employee Benefits",IIf([PHOTOS] Like "*","Photos",IIf([OFFICE_EXP] Like "*","Office Expense"IIf([TRANSP] Like "*","Transportation",IIf([OTHER_EXP] Like "*","Other Expenses","" ))))))))))))))))))); If I keep all the above fields in the expression, I get the message: "the expression you entered is too complex". If, however, I truncate the expression down to certain length, say up to [OTHER_BEN], then It returns the right label (Salaries and Benefits to Other Benefits) in [TRANS_CATEGORY], and nothing in all others (if I specify "" in the last If statement). I want to go beyond this limitation from the first category (salaries & Benefits) all the way to the last category (Other expenses). Maybe the if Function like in excel has a n number of nesting possible or should I take another approach to be able include all my parameters in the query? Please advise and thanks a lot! "Dale Fye" wrote: Need a few more details if we are going to give you any meaningful help. What are the tables and fields in your query, what does your IIF( ) statement look like now? What are you actually trying to accomplish? Dale -- Email address is not valid. Please reply to newsgroup only. "Yamou" wrote: I have about 20 parameters and can't seem to fit them all in one nested iif expression. Is there a way to create an expression that would cycle through those twenty (field) parameters? |
|
Thread Tools | |
Display Modes | |
|
|