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
|
|||
|
|||
#Error when using IIF statement in a query
Hi,
I am trying to create a query that has one of the fields, instead of being linked to a table, calculating a margin based on the value in a field, [Quote_ELC_Type]. The statement is listed below. This statement returns an #error in the Customer Margin field when running the query. As soon as I change the calculation from the iif statement to a simple calculation (without any iif statements), the field returns a correct calculation. Any suggestions on what is wrong with this statement? I hope I have provided enough information. If not, please post back. Customer Margin: IIf([Quote_ELC_Type]=1,1-([ELC West]/[Quote_SuggestedRetail]),IIf([Quote_ELC_Type]=2,1-([ELC East]/[Quote_SuggestedRetail]),IIf([Quote_ELC_Type]=3,1-([ELC Customer]/[Quote_SuggestedRetail]),IIf([Quote_ELC_Type]=4,1-([Quote_ELC_IDC]/[Quote_SuggestedRetail]),0)))) Thanks in advance. Regards, A. Crawford |
#2
|
|||
|
|||
#Error when using IIF statement in a query
Are any of these conditions present or possible?:
[Quote_SuggestedRetail] = 0 [ELCWest] = NULL [ELCEast] = NULL [ELCCustomer] = NULL any of these could cause trouble and should be guarded against elsewhere in the query or in the IIF string explicitly. " wrote: Hi, I am trying to create a query that has one of the fields, instead of being linked to a table, calculating a margin based on the value in a field, [Quote_ELC_Type]. The statement is listed below. This statement returns an #error in the Customer Margin field when running the query. As soon as I change the calculation from the iif statement to a simple calculation (without any iif statements), the field returns a correct calculation. Any suggestions on what is wrong with this statement? I hope I have provided enough information. If not, please post back. Customer Margin: IIf([Quote_ELC_Type]=1,1-([ELC West]/[Quote_SuggestedRetail]),IIf([Quote_ELC_Type]=2,1-([ELC East]/[Quote_SuggestedRetail]),IIf([Quote_ELC_Type]=3,1-([ELC Customer]/[Quote_SuggestedRetail]),IIf([Quote_ELC_Type]=4,1-([Quote_ELC_IDC]/[Quote_SuggestedRetail]),0)))) Thanks in advance. Regards, A. Crawford |
#3
|
|||
|
|||
#Error when using IIF statement in a query
Bruce Meneghin wrote: Are any of these conditions present or possible?: [Quote_SuggestedRetail] = 0 [ELCWest] = NULL [ELCEast] = NULL [ELCCustomer] = NULL any of these could cause trouble and should be guarded against elsewhere in the query or in the IIF string explicitly. Hi Bruce, Yes, they are possibilities. However, in the data I used to test this query, none of these fields are 0 or NULL. Let me post some additional background info on the fields you are inquiring about. They all have a vaule in them. [Quote_SuggestedRetail] can either be empty or have a value greater than zero. The other three fields are calculated fields. How could I guard against these possibilities? Any suggestions would be greatly appreciated. Thanks in advance. Regards, A. Crawford |
#4
|
|||
|
|||
#Error when using IIF statement in a query
The other three fields are calculated fields.
Where are the calculations made? If in this query that is a problem. Instead of [Quote_SuggestedRetail] use the calculations. " wrote: Bruce Meneghin wrote: Are any of these conditions present or possible?: [Quote_SuggestedRetail] = 0 [ELCWest] = NULL [ELCEast] = NULL [ELCCustomer] = NULL any of these could cause trouble and should be guarded against elsewhere in the query or in the IIF string explicitly. Hi Bruce, Yes, they are possibilities. However, in the data I used to test this query, none of these fields are 0 or NULL. Let me post some additional background info on the fields you are inquiring about. They all have a vaule in them. [Quote_SuggestedRetail] can either be empty or have a value greater than zero. The other three fields are calculated fields. How could I guard against these possibilities? Any suggestions would be greatly appreciated. Thanks in advance. Regards, A. Crawford |
#5
|
|||
|
|||
#Error when using IIF statement in a query
KARL DEWEY wrote: The other three fields are calculated fields. Where are the calculations made? If in this query that is a problem. Instead of [Quote_SuggestedRetail] use the calculations. The calculations are made in the same query, just a different field. Could you please explain what kind of problems this creates? The [Quote_SuggestedRetail] is not a calculated field. It is typed in. What do you mean by Instead of [Quote_SuggestedRetail] use the calculations. ? Thank you for your help. Regards, A. Crawford |
#6
|
|||
|
|||
#Error when using IIF statement in a query
For instance if you have --
[Price]*[QTY] As [Cost] Then do not use this -- [Cost]* 0.085 As [Tax] You need to do this -- [Price]*[QTY] * 0.085 As [Tax] and ([Price]*[QTY]) + ([Price]*[QTY] * 0.085) As [Total Cost] " wrote: KARL DEWEY wrote: The other three fields are calculated fields. Where are the calculations made? If in this query that is a problem. Instead of [Quote_SuggestedRetail] use the calculations. The calculations are made in the same query, just a different field. Could you please explain what kind of problems this creates? The [Quote_SuggestedRetail] is not a calculated field. It is typed in. What do you mean by Instead of [Quote_SuggestedRetail] use the calculations. ? Thank you for your help. Regards, A. Crawford |
#7
|
|||
|
|||
#Error when using IIF statement in a query
You have extraneoud ) marks in you code.
Customer Margin: IIf([Quote_ELC_Type]=1,1-([ELC West]/[Quote_SuggestedRetail], IIf([Quote_ELC_Type]=2, 1-([ELC East]/[Quote_SuggestedRetail], IIf([Quote_ELC_Type]=3,1-([ELC Customer]/[Quote_SuggestedRetail],IIf([Quote_ELC_Type]=4,1-([Quote_ELC_IDC]/[Quote_SuggestedRetail]),0)))) When you get this deeply nested, you may want to condiser using the Switch function: Customer Margin: Nz(Switch([Quote_ELC_Type]=1, 1-([ELC West]/[Quote_SuggestedRetail], [Quote_ELC_Type]=2, 1-[ELC East]/[Quote_SuggestedRetail], [Quote_ELC_Type]=3,1-([ELC Customer]/[Quote_SuggestedRetail], [Quote_ELC_Type]=4, 1-[Quote_ELC_IDC]/[Quote_SuggestedRetail]), 0) " wrote: Hi, I am trying to create a query that has one of the fields, instead of being linked to a table, calculating a margin based on the value in a field, [Quote_ELC_Type]. The statement is listed below. This statement returns an #error in the Customer Margin field when running the query. As soon as I change the calculation from the iif statement to a simple calculation (without any iif statements), the field returns a correct calculation. Any suggestions on what is wrong with this statement? I hope I have provided enough information. If not, please post back. Customer Margin: IIf([Quote_ELC_Type]=1,1-([ELC West]/[Quote_SuggestedRetail]),IIf([Quote_ELC_Type]=2,1-([ELC East]/[Quote_SuggestedRetail]),IIf([Quote_ELC_Type]=3,1-([ELC Customer]/[Quote_SuggestedRetail]),IIf([Quote_ELC_Type]=4,1-([Quote_ELC_IDC]/[Quote_SuggestedRetail]),0)))) Thanks in advance. Regards, A. Crawford |
Thread Tools | |
Display Modes | |
|
|