Tech Support Guy banner
Status
Not open for further replies.
1 - 9 of 9 Posts

·
Registered
Joined
·
9 Posts
Discussion Starter · #1 ·
Hi there,

I'm creating a nested Iff statement in a query to return the corresponding cost for each value:

0-500 = 6,48
501-750 = 4,32
751+ = 2,16

2,16 displays correctly for values over 751.
4,32 displays correctly for values 501-750
6,48 displays correctly for values 0-500. HOWEVER! Amongst the 6,48 output, there are values that should have been caught in the over 751 value region (2,16), for example numbers such as '1170', etc. Below is my statement:

Cost: IIf([Value]>="0" And [Value]<="500","6,48",IIf([Value]>="501" And [Value]<="750","4,32",IIf([Value]>="751","2,16",Null)))

If anyone could help in any way would be very appreciated, I am very unsure as to why some of the values that should be in the 2,16 (over 751) are in the 6,48. Thank you!
 

·
Registered
Joined
·
9 Posts
Discussion Starter · #3 ·
Hi there! Thank you for you response.

These are indeed text values, the dataset was imported and it defaulted to that. If I change the type to numeric, then run the query, in the Cost output field, every field is filled in with #Error. When I remove the quotation marks and run it again, it throws an error about about the commas.

I'm not sure as to why this is happening
 

·
Registered
Joined
·
19,896 Posts
On the excel sheet you supplied the cells have a caution against them saying that they are text or there is an apostrophe in front of the value. You cannot see it.
So in the first Cell click on the Editing line and place the cursor at the front of the value and press the delete button.
The value will now appewar as a number formatted as Currency or 2 decimal points.
 
1 - 9 of 9 Posts
Status
Not open for further replies.
Top