building an expression in ms access

[AbbasBD]

I want to build an expression for House Rent (HR) in MS Access.

If Basic Salary (BS) is =>5000, HR is 50% of BS but not less than 2250,

if BS is between 5001 and 10800, HR is 45% of BS but not less than 2500 and

if BS is between 10801 and 21600, HR is 40% of BS but not less than 4800

[jimatqsi]

Is this appearing in a query, or in code on a form?

It appears you made a mistake in your criteria, I think you mean if BS is less than or = 5000 then HR is 50%

On a form you could build a series of if tests

if BS >21600 then
' something happens here?
elseif BS >= 10801 then
HR = BS *.4
if HR < 4800 then HR=4800
Elseif BS >=5001 then
HR=BS*.45
if HR<2500 then HR=2500
elseif BS <=5000 then
HR=BS * .5
if HR<2250 then HR=2250
endif
 

You might want to add some rounding, like this

HR=Round(BS*.45,0) ' whole dollars only

Jim

[AbbasBD]

Sorry, my knowledge is quite basic, I want to build expression for HR in design view something like:

HR:
IIf([BS]<=5000,[BS]*0.5,But if[BS]*0.5<2250,"2250",
IIf([BS]>5000 And [BS]<=10800,[BS]*0.45,But if[BS]*0.45<2500,"2500",
IIf([BS]>10800 And [BS]<=21600,[BS]*0.4,But if[BS]*0.4<4800,"4800")))

Here, I do not know what may be correct expression for 'But if'... pl help. thanks.

[Jerry Maiapu]

I assume that calculation is done on a query. (In query Design View)

After a analysis (go through). I think this will work try it out:
copy&paste

HRent: IIf([BS]<=5000,IIf([BS]*0.5<2250,2250,[BS]*0.5),
IIf([BS]<=10800,IIf([BS]*0.45<2500,2500,[BS]*0.45),
IIf([BS]<=21600,IIf([BS]*0.4<4800,4800,[BS]*0.4))))

Sampe Output

BS    HRent
4000    2250
5000    2500
6000    2700
7000    3150
8000    3600
9000    4050
10000    4500
11000    4800
12000    4800
13000    5200
14000    5600
15000    6000
16000    6400
.............

NB:You can change column Name HR and give your own name.

Hope it helps:

Jerry

[AbbasBD]

dear jerry

thank u very much for your prompt reply, it worked greatly!

AbbasBD

[Jerry Maiapu]

Please select as best Answer.

Glad I could help

[AbbasBD]

I want to build an expression in MS Access Design View for ‘Personal Basic’ [PB] where [PB] is equal or immediate greater than ‘New Basic Minimum’ [NBM] by adding one or more ‘Increment Rate’ [IR] to ‘Basic Start’ [BS] ie,

[PB]=[BS]+([IR]*x) {where [BS]+([IR]*x)>=[NBM]}

For example:

[PB]=4500+(240*2) {here [NBM]=4890<4500+(240*2) ie, x=2 but Not x=1 or x=3}

[PB]=4980 {where 4980>=4890}

Thanks

[Jerry Maiapu]

Hi AbbasBD, before I post suggestions to your question, please write a new question for this threat.

Your Question Title would be something like: Query Criteria not Working.
Thanks

[AbbasBD]

Jerry Maiapu

Thanks for your concern. Sorry for not being able to make you understand... however, just few hours back i’ve found out a solution that works for me:

PB:
(((([NBM]-[BS])\[IR])+1)*[IR])+[BS]

However, i welcome you, should you have anything better.

Thanks.

[Jerry Maiapu]

If the calculation of PB is based on a condition, then you should have a IIF clause.

For example:

If NB>=NBM then BS+IR
else
something else';;;This part is missing

Though the syntax of IIF is

IIF (condition,True Part,False Part)

the latter is an example.

According to your solution:

(((([NBM]-[BS])\[IR])+1)*[IR])+[BS]

You're simply doing some straight forward calculation.

It is not doing what you expected it to do. ie. it is NOT checking if [PB] is equal or immediate greater than ‘New Basic Minimum’ [NBM] by adding one or more ‘Increment Rate’ [IR] to ‘Basic Start’ [BS].

If it follows a sequence then hope your solution is a sequence formula
like 2n + 1 where n is the term in the sequence. (Maths you know)

Hope am clear.

[AbbasBD]

Dear Maiapu

I failed to apply iif function, i've no idea about 2n+1 sequence formula... however, i tried the following that didn't work:

PB:
IIf([NBM]>=(1*[IR])+[BS],(1*[IR])+[BS],
IIf([NBM]>=(2*[IR])+[BS],(2*[IR])+[BS],
IIf([NBM]>=(3*[IR])+[BS],(3*[IR])+[BS],
IIf([NBM]>=(4*[IR])+[BS],(4*[IR])+[BS],
IIf([NBM]>=(5*[IR])+[BS],(5*[IR])+[BS]
)))))

Sure i've something wrong with the formula that i cannot sovle... waiting ur suggestion.

thanks

[Jerry Maiapu]

AbbasBD,


Can u explain a bit more on on what you are trying to achieve. I might by then help you.

From what you posted, the query expression is confusing itself in terms of evaluating the parts in the IIF clause.

Try and change all the > to < like this; run it and see if it meet your expectations:

PB:
IIf([NBM]<=(1*[IR])+[BS],(1*[IR])+[BS],
IIf([NBM]<=(2*[IR])+[BS],(2*[IR])+[BS],
IIf([NBM]<=(3*[IR])+[BS],(3*[IR])+[BS],
IIf([NBM]<=(4*[IR])+[BS],(4*[IR])+[BS],
IIf([NBM]<=(5*[IR])+[BS],(5*[IR])+[BS]
)))))

Looks like numbers 1 to 5 are not constant ie will not stop to increment by one. Is that correct.?

What is that you are really trying to do?
Tell me.

Try the above and post back any query.
Cheers

[NeoPa]

AbbasBD:
If Basic Salary (BS) is =>5000, HR is 50% of BS but not less than 2250,

This is a strange question considering 50% of the lowest qualifying value (5,000) is 2,500 anyway. If the lowest possible value is 2,500 why would you want to check that it's greater than 2,250?

** Edit **
Forget that. I see it's been covered already, but the same is true of the next one down. These simple details should be checked and corrected before posting the question, otherwise it just wastes people's time.

[AbbasBD]

@Jerry Maiapu

Yes right you are! 1, 2, 3, 4, 5 are not constant.

I want to find expression for Personal Basic [PB] that will be equal or greater than New Basic Min [NBM] by adding Increments [IR] to Basic Start [BS] (Here, always BS<NBM).

If [NBM]>=[BS]+1*[IR], its OK, but if by adding one IR doesn't make it, than should add two IR, ie, [NBM]>=[BS]+2*[IR]... Here, IR is incremental, may be nine IR need to be added... something like this.

Well, changing all > to < is not helping. May be I could clear... waiting for your reply.

Thanks.

[AbbasBD]

NeoPa

Thanks for your observation. I'll be careful... next time. However, correct one should be:

If BS<=5000, HR=BS*50% but not less than 2250,

Stay fine.