#Error when using IIF statement in a query

[antcraw]

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

 

[Bruce Meneghin]

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

 

[antcraw]

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

 

[Klatuu]

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)

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

 

[KARL DEWEY]

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.

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

 

[antcraw]

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

 

[KARL DEWEY]

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]

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

 

[antcraw]

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]

I followed your advice. Unfortunately, this creates a very long SQL
statement. It returned the message "The string returned by the builder
was too long." Since shortening, due to the necessary calculations, is
not really an option, are there any other possibilities?

Thanks in advance.
All suggestions are greatly appreciated.

Regards,
A. Crawford

 

[KARL DEWEY]

You can try having one query do part of your calculations and then another to
complete them but I think I read that Access take all queries as if one and
still might see it as too big.

An alternative is the make the first query a make-table to create a
temporary table and then the next query off the temp table.

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]

I followed your advice. Unfortunately, this creates a very long SQL
statement. It returned the message "The string returned by the builder
was too long." Since shortening, due to the necessary calculations, is
not really an option, are there any other possibilities?

Thanks in advance.
All suggestions are greatly appreciated.

Regards,
A. Crawford

 

[antcraw]

You can try having one query do part of your calculations and then another to
complete them but I think I read that Access take all queries as if one and
still might see it as too big.

An alternative is the make the first query a make-table to create a
temporary table and then the next query off the temp table.

Thank you for your reply. After playing around with the IIF statement a
bit more, I realized that it only returned the #error on the true
evaluation. When the IIF statement evaluated false, it would return
data. So I enclosed the [Quote_ELC_Type] value in quotes and it now
works. Not sure how, but it works.
It now looks like this:

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 again for all your help.

Regards,
A. Crawford

 

[KARL DEWEY]

Your field [Quote_ELC_Type] would be a text field instead of a number field.

You can try having one query do part of your calculations and then another to
complete them but I think I read that Access take all queries as if one and
still might see it as too big.

An alternative is the make the first query a make-table to create a
temporary table and then the next query off the temp table.

Thank you for your reply. After playing around with the IIF statement a
bit more, I realized that it only returned the #error on the true
evaluation. When the IIF statement evaluated false, it would return
data. So I enclosed the [Quote_ELC_Type] value in quotes and it now
works. Not sure how, but it works.
It now looks like this:

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 again for all your help.

Regards,
A. Crawford

 

[antcraw]

Your field [Quote_ELC_Type] would be a text field instead of a number field.

Yes, absolutely. I wish the obvious sometimes would come easier. It
all makes perfect sense now.
Would it be better practice to make this field a number field instead
of a text field? My reason for making it a text field was that it will
not be included into any calculations.

TIA.
Regards,
A. Crawford