IIF Statements maybe IIFElse Statements (1 Viewer)

pbate

New member
Joined
Aug 20, 2012
Messages
3
I am not sure which I should use. I have a table that has three fields ID, Percent, Range, the id field is just that and automatic, the Percent field is 3%, 5% and 7%, the Range is <11, >10<21, and >21, now the purpose of this is because our contract says that if you buy less than 11 dogs you get 3% off of your purchase price, if you by between 11 to 20 dogs you get 5% off your purchase price, and if you buy 21 or more you get 7% off your purchase price. I know that I will have a form that tells me how many dogs are purchased and what the total price is. Have I set this up wrong to start with? I know that Access will do an if or if else and an if, then statements. I have never been successful with these nor do I know if they will help me with this problem. Can any one give me guidance. thank you
 

MSAccessRookie

AWF VIP
Joined
May 2, 2008
Messages
3,428
I am not sure which I should use. I have a table that has three fields ID, Percent, Range, the id field is just that and automatic, the Percent field is 3%, 5% and 7%, the Range is <11, >10<21, and >21, now the purpose of this is because our contract says that if you buy less than 11 dogs you get 3% off of your purchase price, if you by between 11 to 20 dogs you get 5% off your purchase price, and if you buy 21 or more you get 7% off your purchase price. I know that I will have a form that tells me how many dogs are purchased and what the total price is. Have I set this up wrong to start with? I know that Access will do an if or if else and an if, then statements. I have never been successful with these nor do I know if they will help me with this problem. Can any one give me guidance. thank you
With a little creative thinking, you might be able to make due with a Minimum Range only doing something like this:

Code:
[FONT=Courier New]  ID    DiscountPercent    MinRange[/FONT]
[FONT=Courier New]  1         3                  0[/FONT]
[FONT=Courier New]  2         5                 11[/FONT]
[FONT=Courier New]  3         7                 21[/FONT]
Your Code would have to find the largest MinRange that is not bigger than the value that you have selected, and use the DiscountPercent associated with it.

-- Rookie
 

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,580
Yes you have set it up wrong, but your close. First, 'Percent' is a bad name for a field, because its (I think) a reserved name in Access. Name it something more descriptive (i.e. Discount). Second, you don't really need an ID field in this table, its not going to kill anyone, but I'm not a big fan of having fields you will never use.

On to your problem. You can do this 2 possible ways, Method 1 would be to assign a Discount amount to every possible value:

Discount, DogsBought
3%, 1
3%, 2
...
3%, 10
5%, 11
5%, 12
...
7%, 21
7%, 22
...

Method 2 would be similar to what you are attempting, but would explicitly define the range using 2 fields like so:

Percent, RangeLow, RangeHigh
3%, 1, 10
5%, 11, 20
7%, 21, 9999

Implementing each Method would be different as well. Using Method 1 you could just bring in that table and link to the DogCount field of your Query/Table to determine the discount. For Method 2 you would need to create a calculated field in a query, use the DogCount field in a DCount to determine which specific range it falls in and then return that discount.
 

pbate

New member
Joined
Aug 20, 2012
Messages
3
I thank you for your help and will try these solutions and see which one works best. Thanks again. I will let you know what I find.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom