A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#Error when using IIF statement in a query



 
 
Thread Tools Display Modes
  #11  
Old November 9th, 2006, 07:40 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default #Error when using IIF statement in a query

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

" wrote:


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


  #12  
Old November 9th, 2006, 08:43 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 10
Default #Error when using IIF statement in a query


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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.