I don't know SQL or VBA

[Kathleen Badeau]

I have used simple queries before but I am tackling something beyond my current realm of experience.

I am trying to set up a budget field that will calculate the budget amount based on rate assessed to a certain parameter of dollar amounts. I.E.

Grant award amount >$1,000,000 then the budget for evaluation would be Grant Award Amount *.10
However, if the Grant award amount >=$300,000 but <$1,000,000 then Evaluation budget would be Grant Award Amount *.12
or if the Grant Award amount >=100,000 but <$300,000 then Evaluation budget would be Grant Award Amount *.15
or if the Grant Award amount <$100,000 then Evaluation budget would be Grant Award Amount * 0

Am I out of my mind in thinking that I can do this sort of calculation in Access?

 

[glynch]

I am writing from a PC that doesn't have Access installed on it so I can't test it right now but I think this will work.

Create a new query in design mode. Add the table with the grant data to this query and pull the grant amount field down into the fields section of the QBE. Then in another field enter this:

EvalBudget:iif(GrantAmt >= 1000000,GrantAmt * .1,iif(GrantAmt >=300000 and < 1000000,GrantAmt * .12,iif(GrantAmt >=100000 and < 300000,GrantAmt * .15,0)))

Hopefully I got the syntax correct, try it and see how it works. These "immediate if" statements are kind of slow when inserted into queries, but without knowing VBA, it might be your best shot.

HTH

 

[Kathleen Badeau]

Help with SQL And VBA

Thank you so much for your help...it got me started in the right direction. What ended up working was this:

IIf([Amount Awarded]>=1000000,[Amount Awarded]*0.1,IIf([Amount Awarded]>=300000,[Amount Awarded]*0.12,IIf([Amount Awarded]>=100000,[Amount Awarded]*0.15,IIf([Amount Awarded]<100000,[Amount Awarded]*0)))) The expression did not like the additional < opperand in the command.

All the calculations work out perfectly!!!! What I didn't know how to do was use the IIF statement (nothing listed under the access help, and the structure needed to input into the query.

You've made my day!!!