#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

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

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

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

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

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

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

KARL DEWEY wrote:
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

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.

" wrote:


KARL DEWEY wrote:
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 wrote:
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