Multiple IIf statement not working! (1 Viewer)

thudson

Old Programmer
Local time
Today, 11:23
Joined
Apr 29, 2011
Messages
68
I am developing a database to track my eBay sales and I have written an expression to work out the cost for International postage, based on the zone they are in. I have decided to display 2 different text boxes for the 2 postage zones.
The cost is determined based on the weight.
Here is the expression I have written:
Code:
IIf( [International Zone] =1 And [Weight g] Between 0 And 100 ,3.30, 
IIf( [International Zone] = 1 And [Weight g] Between 101 And 250 ,4.85, 
 IIf( [International Zone] = 1 And [Weight g] Between 251 And 500,7.10 ,
 IIf( [International Zone] = 1 And [Weight g]  Between 501 And 750 , 9.15,0))))
It does not error but does not give the correct answer.
Lets say the weight is 150 and the zone is 1. the answer should be 4.85, but it gives 0.
I have checked on the web and the logic is correct.
In the table the Zone and Zone Cost are general numbers.
Can any one see where I am going wrong?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:23
Joined
Aug 30, 2003
Messages
36,123
What's the data type of [weight g] (spaces not worth the trouble in the long run)? Try

IIf( [International Zone] =1 And [Weight g] < 100 ,3.30,
IIf( [International Zone] = 1 And [Weight g] < 250 ,4.85,
IIf( [International Zone] = 1 And [Weight g] < 500,7.10 ,
IIf( [International Zone] = 1 And [Weight g] < 750 , 9.15,0))))
 

thudson

Old Programmer
Local time
Today, 11:23
Joined
Apr 29, 2011
Messages
68
Don't know what was going on, but it works now??
I was choosing the zone then moving to a new field or even a new record, but it was not changing. Happened to close the form and noticed it had done the calculation. I have put a note beside it to press F5 which updates it immediately.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:23
Joined
Aug 30, 2003
Messages
36,123
Glad you got it working. I'd have those values in a table, so they're more easily maintained and instead of a complex formula you have a simple lookup. 4 fields: zone, weight min, weight max, and cost.
 

Users who are viewing this thread

Top Bottom