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

[Guest]

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,

 

[Allen Browne]

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

 

[Guest]

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

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

 

[Guest]

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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
"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,

 

[Guest]

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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
"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,

 

[Guest]

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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
"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,