Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-16-2018, 09:16 AM   #1
thudson
Old Programmer
 
Join Date: Apr 2011
Location: Hastings, E Sussex, UK
Posts: 66
Thanks: 4
Thanked 1 Time in 1 Post
thudson is on a distinguished road
Multiple IIf statement not working!

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?

thudson is offline   Reply With Quote
Old 11-16-2018, 09:25 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,196
Thanks: 10
Thanked 3,895 Times in 3,838 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multiple IIf statement not working!

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))))
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-16-2018, 09:59 AM   #3
thudson
Old Programmer
 
Join Date: Apr 2011
Location: Hastings, E Sussex, UK
Posts: 66
Thanks: 4
Thanked 1 Time in 1 Post
thudson is on a distinguished road
Re: Multiple IIf statement not working!

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.

thudson is offline   Reply With Quote
Old 11-16-2018, 10:06 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,196
Thanks: 10
Thanked 3,895 Times in 3,838 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multiple IIf statement not working!

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.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
IIF Statement not working Jrmrosebud SQL Server 8 04-13-2015 09:09 AM
two If statement not working penfold1992 Modules & VBA 1 12-05-2012 12:50 PM
IF & AND statement not working ian_ok Forms 1 10-30-2010 02:37 AM
IIF statement not working twychopen22 Queries 10 08-04-2006 09:04 AM
If statement not working Workaholic Modules & VBA 10 08-20-2004 10:26 AM




All times are GMT -8. The time now is 01:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World