Changing Calc Field in a Query with VBA?

[annt]

I have a query that contains a field with a calculation (a long series of if...then) Forgive me if my terminology is not right.

I would like to be able to change the calculation in that field depending upon user-input on a form.

Here is an illustraion of what I would like to do:

Take query named CALC based on a table with Columns A and B, both containing numeric data. My query includes a column(Field) that I name RESULT: A+B and the query's RESULT adds the data in A & B.

However, the user decides the B should be subtracted from A and via a checkbox on a form, a macro runs (or some sort of programming) that changes the RESULT field in the CALC to be RESULT: A-B.

Can I do this in Access? How?

 

[Uncle Gizmo]

So you want to look at each record individually and allow the operator to specify that in some circumstances the "Results" column will contain A+B and sometimes A-B?

 

[annt]

clarification

Actually, no. The calculation change would apply to all records in the query.

On the input form, I'd want to have a checkbox for A-B and a checkbox for A+B. Which box that is check would determine which calc was in the query.

Thanks for your response!

 

[dcx693]

How about trying to use an IIf function in your query? It can check to see if a field in the form says "-" or "+". The funtion can then take the addition of difference of the two numbers. The syntax would be something like:
IIf(Forms!formname!fieldname="+",[A]-,[A]+)

 

[annt]

Thanks! That is a very good suggestion.

My only concern is that the main calculation is already a long string of iif statements. Is there a limit on how long an iif statement in a query can efficiently run? The second calc is not long and could be added to the main iif statement. However...

Would I have more flexibility if I converted it to VBA code? A module? I don't think I even know how to begin changing it, but would love to know how to.

 

[annt]

Calculation

Here is the main calculation. How would this be written in VBA? Or as a function in a module?

Calculation: IIf([CPR]<[Min] And [ODH]=On,[Min]-[CPR],IIf([CPR]<[Mid],(IIf([T]=0,(IIf([Mid]-[CPR]<[MIA],[MIA],[Mid]-[CPR])),(IIf([CPR]<[T],(IIf([T]-[CPR]<=[MIA],[MIA],[T]-[CPR])),[CPR]*[POM])))),(IIf([CPR]<[Max],(IIf([CPR]*(1+[POM])>[Max],(IIf([max]-[CPR]<=[MIA],[MIA],[Max]-[CPR])),[CPR]*[POM])),[AOM]))))

Thank you for your help!

 

[OPMCoordinator]

how many different ways would they change the main calc?

if only a few set ways, you could provide toggle buttons (say three, one for add, one for subtract, one for multiply)and do the approprite calculation in vba eg

***************
sub choosecalc()
dim as as int
dim b as int
dim result as int

set your a and b here

select case togglebutton
case 1
result = a + b
case 2
result = a - b
case 3
result = a * b
end select

do whatever with result here

end sub
**************************
(dont look too hard at the code!)

that would work if you do not allow too much flexibility.

alternatively you could work it out all ways, display all ways and the user can choose what they want to use.

hth

mike c