multiple condition (1 Viewer)

Hello everyone,
I hope someone can help me about my vba in access

how to write the code for this condition

a = 0 to 5
b = 5 to 10
c = 10 to 15
d = 15 to 20
e = 20 to 25
f = 25 to above

if G is equal to a then
H is = 0
if G is equal to b
H = G minus 5 multiply by 0.1
if G is equal to c
H = G minus 10 multiply by 0.2
if G is equal to d
H = G minus 15 multiply by 0.3
if G is equal to e
H = G minus 20 multiply by 0.4
if G is equal to f
H = G minus 25 multiply by 0.5

Thank

Create a table for those value:

TblLimits:
Start (integer)
End (integer)
Value(single)

Start End value
0 5 0
6 10 .1
11 15 .2
...
26 99999 .5


You can use dlookup() or exoression in query.

Dlookup("value", "tblLimits", nz([g] ,0) & " between start and end")

Your logic appears incorrect.?
A value can be in another range if at the top end of the previous range?
eg if value is 5 it can be in range a or range b ?

abshmo5 said:

Hello everyone,
I hope someone can help me about my vba in access

how to write the code for this condition

a = 0 to 5
b = 5 to 10
c = 10 to 15
d = 15 to 20
e = 20 to 25
f = 25 to above

if G is equal to a then
H is = 0
if G is equal to b
H = G minus 5 multiply by 0.1
if G is equal to c
H = G minus 10 multiply by 0.2
if G is equal to d
H = G minus 15 multiply by 0.3
if G is equal to e
H = G minus 20 multiply by 0.4
if G is equal to f
H = G minus 25 multiply by 0.5

Thank

Click to expand...

This is a PERSONAL preference but when you have a "ladder" like that, I prefer to code it using SELECT CASE statements.


I suggest this because it is less cluttered and easier to read than an "If ... Then ... ElseIf ... ElseIf ... Else ... End If" sequence. And since you are using fractional multipliers, you can pick fractional ranges.

The_Doc_Man said:

This is a PERSONAL preference but when you have a "ladder" like that, I prefer to code it using SELECT CASE statements.

Code:Copy to clipboard

SELECT CASE G
    CASE 0 TO 4.99
        H = 0
    CASE 5 TO 9.99
        H = ( G - 5 ) * 0.1
    CASE 10 TO 14.99
        H = ( G - 10 ) * 0.2
    ...                             ' other cases go here
    CASE ELSE                'covers the 25 and over case
        H = ( G - 25 ) * 0.5
END SELECT

I suggest this because it is less cluttered and easier to read than an "If ... Then ... ElseIf ... ElseIf ... Else ... End If" sequence. And since you are using fractional multipliers, you can pick fractional ranges.

Click to expand...

Wow this works too.
Thanks

I lean towards arnelgp's solution in principle. Usually better to store such parameters in a table than hardcode it. This allows parameters to change without altering code.

However there are potential issues with gaps between the ranges in his solution since one range ends at 5 and the next range starts at 6. If the value being tested is 5.5 then it will be missed.

It is much safer to only specify the upper cut off for a range and design the query to use only the one number and calculate the extent of the range from the other records.

The calculation is most efficiently done as part of the query if the circumstances permit. Running a DLookup for each record is best avoided if possible but it is a way around queries that might otherwise be not updateable.

Galaxiom said:

It is much safer to only specify the upper cut off for a range and design the query to use only the one number and calculate the extent of the range from the other records.

Click to expand...

I agree with Greg on using only one number. I answered a somewhat similar question a while back which just uses one number. See slide 15 on this page:-

http://www.niftyaccess.com/pull-out-percentages/