Store Calculation

G

Guest

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?
 
G

Guest

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?

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

--
KARL DEWEY
Build a little - Test a little


DRMOB said:
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?
 
D

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!)


DRMOB said:
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?

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

--
KARL DEWEY
Build a little - Test a little


DRMOB said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top