Store Calculation

[DRMOB]

I have a form that has 2 subforms on it. One of the subforms has 2 fields
that are based on calculations performed in a query. The 1st calc is
"Total Approved: Sum([QtyApproved]*[CostApproved])" The second is "Less 10%:
IIf([Total Approved]>1000,[Total Approved]*0.9)".
FYI, the first calculation always shows on my form; the second will only
show if the 1st is greater than 1000. Now I have a 3rd field "Total Granted"
where I want to store what is in the 2nd calculation, if >1000; if less than
1000, than I want to store the 1st calculation in Total Granted.

I was able to get the 1st calculation to store, it's the IF statement that
is getting me.
=(ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Approved])

Can you help?

 

[KARL DEWEY]

Total Granted: IIf([Total Approved]>1000,[Total Approved]*0.9, [Total
Approved])

 

[DRMOB]

I put this code in the query of one of the subforms and it works fine. What
I can't seem to do is to get the result to actually be stored in a field I
call Total Granted within the main table. Any suggestions on that?

Total Granted: IIf([Total Approved]>1000,[Total Approved]*0.9, [Total
Approved])

--
KARL DEWEY
Build a little - Test a little

I have a form that has 2 subforms on it. One of the subforms has 2 fields
that are based on calculations performed in a query. The 1st calc is
"Total Approved: Sum([QtyApproved]*[CostApproved])" The second is "Less 10%:
IIf([Total Approved]>1000,[Total Approved]*0.9)".
FYI, the first calculation always shows on my form; the second will only
show if the 1st is greater than 1000. Now I have a 3rd field "Total Granted"
where I want to store what is in the 2nd calculation, if >1000; if less than
1000, than I want to store the 1st calculation in Total Granted.

I was able to get the 1st calculation to store, it's the IF statement that
is getting me.
=(ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Approved])

Can you help?

 

[Douglas J. Steele]

Why do you want to store the calculation?

As fellow MVP John Vinson likes to say, "Storing derived data such as this
in your table accomplishes three things: it wastes disk space; it wastes
time (almost any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the underlying fields
is subsequently edited, you will have data in your table WHICH IS WRONG, and
no automatic way to detect that fact. "

Just redo the calculation whenever you need it, either as a calculated field
in a Query or just as you're now doing it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I put this code in the query of one of the subforms and it works fine.
What
I can't seem to do is to get the result to actually be stored in a field I
call Total Granted within the main table. Any suggestions on that?

Total Granted: IIf([Total Approved]>1000,[Total Approved]*0.9, [Total
Approved])

--
KARL DEWEY
Build a little - Test a little

I have a form that has 2 subforms on it. One of the subforms has 2
fields
that are based on calculations performed in a query. The 1st calc is
"Total Approved: Sum([QtyApproved]*[CostApproved])" The second is "Less
10%:
IIf([Total Approved]>1000,[Total Approved]*0.9)".
FYI, the first calculation always shows on my form; the second will
only
show if the 1st is greater than 1000. Now I have a 3rd field "Total
Granted"
where I want to store what is in the 2nd calculation, if >1000; if less
than
1000, than I want to store the 1st calculation in Total Granted.

I was able to get the 1st calculation to store, it's the IF statement
that
is getting me.
=(ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Approved])

Can you help?