If then statement in a query? 1

[Krash878]

I have a feild that is based on the following Calculation.

MA=Member Assets
TA=Total Association Assets
LF=Legisilative Fee which is 4000 right now
MF=Members portion of Legislative fee.

(MA/TA)LF = MF

Ok not that I have this answer I have to go to the next step.

If MF > 12.00 then MF=MF else MF = 12.00

What I need to know is how to set this up.

Thanks to anyone who can help.

Krash

 

[vb6novice]

Krash,

Can it be assumed that TA, LF, and MA are fields in a table, and that you want a query that produces the field MA as result of the calculation you provided.

If MF is already a field in the table (which is not wise becuase it is the reuslt of a calculation and if one of the other fields changes, it may change the value of the caculation but would not automaticcally change the value of the field), then you need an update query.

If MF is NOT a field in the table, then your query field defenition should be
MF: Iif((MA/TA)LF>12,(MA/TA)LF,12)

 

[Krash878]

Yes, TA, LF, and MA are fields in a table, and I want a query that produces the field MF as result of the calculation I provided.

No MF is not in a Table.

This is what I have now.

Legislative: IIf([LegislativeSub]<12,&quot;12.00&quot;,[LegislativeSub])

Based on
MF: Iif((MA/TA)LF>12,(MA/TA)LF,12)

I need to show every step of the formula for our invoice.
thanks for your help.

My problem was that I was trying to use If( instead of IIf(

Thanks again

Krash

 

[vb6novice]

Krash,

Your formula is not quite correct. Unlike a mathematical formula, Access does not evaluate a variable next to something in parentheses as multiplying one by the other. You must us the * operator between (MA/TA) and LF.

Is it true that MF is the same thing as Legislative, and LegislativeSub = (MA/TA)*LF ?

You are on the right track.

The query should have a field with the definition

LegislativeSub: (MA/TA)*LF

and then another one with

Legislative: IIf([LegislativeSub]<12,12.00,[LegislativeSub])

I don't see why you need the quotes around the 12.00.

 

[Krash878]

hehe I actually have the * in my formula.

Yes Legislative is the same as LegislativeSub, However I have to show every step of the formula on the invoice (legal stuff). If LegislativeSub comes out to be 9.89 then Legislative will be 12.00. If LegislativeSub comes out to be 244.59 then Legislative will be 244.59.

Our members want to see all of that so I give it all to them.

Everything is working perfectly now.

Thanks for the help

Krash

 

[vb6novice]

Krash,

Glad it's working for you.

Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need.