how 2 update a tbl field based on result of a math calculation in

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel Allan - 06 Sep 2004 05:35 GMT

SHi,

here is my situation, my "SalesRegister" table contains fields called
"SaleUnits" , "SalePricePerUnit" and "TotalPrice". I'm calculating
"TotalPrice" by multiplying "SaleUnits" with "SalePricePerUnit" IN A FORM. I
get the correct ammount reflected in "TotalPrice", but how do I update that
"TotalPrice" field into the "SalesRegister" table? Thanks for you help

Cheers,

Reply to this Message

Allen Browne - 06 Sep 2004 05:47 GMT

Hi Daniel

Unless there is a reason why the TotalPrice should sometimes be different
from the calcuation, you must not store it in your table. Instead, use a
calculated field in query. Then you never have to worry about whether you
have updated the field correctly or not.

The calculated field is as simple as typing this into the Field row in your
query:
TotalPrice: [SalesUnits] * [SalePricePerUnit]

Even better, handle the null and convert the result to currency:
TotalPrice: CCur(Nz([SalesUnits] * [SalePricePerUnit],0))

For more information, including how to store the calculated result in your
table if you really need to, see:
Calculated fields
at:
http://members.iinet.net.au/~allenbrowne/casu-14.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> here is my situation, my "SalesRegister" table contains fields called
> "SaleUnits" , "SalePricePerUnit" and "TotalPrice". I'm calculating
[quoted text clipped - 5 lines]
>
> Cheers,

Reply to this Message

Daniel Allan - 07 Sep 2004 05:01 GMT

Hi Allen,

Thanks for you suggestion, I created the query as suggested, i am able to
get correct data from the query output into a field called "TotalPrice" in
the query result window, but how do I extract data from the query result
field into my "Sales Register" table? Thanks

Daniel

> Hi Daniel
>
[quoted text clipped - 15 lines]
> at:
> http://members.iinet.net.au/~allenbrowne/casu-14.html

Reply to this Message

Allen Browne - 07 Sep 2004 08:43 GMT

Daniel, the idea is to use the calculated query field only.
Do not store the result in the table.
Use the query as the source for whereever you need the calculation.

As an analogy, calculated query fields are like calculated cells in Excel.
You should never store the result back into the table, unless you would
store it (the result) in a cell in Excel, i.e. the cell would no longer
contain the formula.

If you do want to store the result, then you must use the AfterUpdate event
of each text box on your form that is involved in the calculation, as in the
2nd example in the web article.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen,
>
[quoted text clipped - 26 lines]
>> at:
>> http://members.iinet.net.au/~allenbrowne/casu-14.html

Reply to this Message

Giorgio - 05 Dec 2004 06:45 GMT

Hi, I'm Giorgio,
your tip was of great help with a simple sum, but did not work with this
formula:
=DateDiff("m",[Birthdate],Now())+Int(Format(Now(),"mm""dd""")<Format([Birthdate],"mm""dd"""))
The queiry keeps asking me for a birhdat which I already posted, and does
not show the result anyway.
Thank you
Giorgio

> Hi Daniel
>
[quoted text clipped - 25 lines]
> >
> > Cheers,

Reply to this Message

JPGeorge - 30 Nov 2005 02:21 GMT

Allen,

I am having the same type of problem. I input hour counts on a subform that
feeds off of a query "100C_Query". I have tried your fix described in this
thread as well as having read your "Calculated fields" page on
allenbrowne.com. Unfortunately, when I run the query, even with data in the
source fields, I get zilch.

The Field Row of my query reads: TotalCommercial: [100C
Query]![Scale]+[100C Query]![OtherEnforcement]+[100C Query]![Patrol]+[100C
Query]![NAS_HMInspections]+[100C Query]![ReinspectionsTime]+[100C
Query]![MCPP]+[100C Query]![Supervisor],

and the SQL view reads: SELECT [100C Query].IDNumber, [100C
Query].ReportDate, [100C Query].MRE, [100C Query].Scale, [100C
Query].OtherEnforcement, [100C Query].Patrol, [100C Query].NAS_HMInspections,
[100C Query].ReinspectionsTime, [100C Query].MCPP, [100C
Query].Supervisor,[100C Query]![Scale]+[100C Query]![OtherEnforcement]+[100C
Query]![Patrol]+[100C Query]![NAS_HMInspections]+[100C
Query]![ReinspectionsTime]+[100C Query]![MCPP]+[100C Query]![Supervisor] AS
TotalCommercial
FROM [100C Query];

Can you advise?

Also, I would like to include some method to delete the requests for data if
null.

> Hi Daniel
>
[quoted text clipped - 25 lines]
> >
> > Cheers,

Reply to this Message

JPGeorge - 30 Nov 2005 02:31 GMT

Allen,

Is there a difference in code if I am trying to add several fields to obtain
the total? I have a query, "100C Query" that collects hour counts, as
integers and I am trying to get a "Total" field to return the sum of several
fields. I tried the code from this thread in my query, but I get only an
empty field in response.

Also, once calculated, I would like the result to be reflected in my input
form. Am I following the wrong logic to have the query do this function or
should I just code the form to do this for me?

TIA,
John

> Hi Daniel
>
[quoted text clipped - 25 lines]
> >
> > Cheers,

Reply to this Message