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 |
#11
|
|||
|
|||
#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
|
|||
|
|||
#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 | |
|
|