#Error when using IIF statement in a query

A

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
 
B

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
 
A

antcraw

Bruce said:
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
 
K

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
 
K

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.

Bruce said:
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
 
A

antcraw

KARL said:
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
 
K

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]

KARL said:
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
 
A

antcraw

KARL said:
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
 
K

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.

KARL said:
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
 
A

antcraw

KARL said:
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
 
K

KARL DEWEY

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

KARL said:
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
 
A

antcraw

KARL said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top