form sum > updates table

[MsCrow]

Hello, I've searched here but didn't find any help so I hope someone can.

I want a sum from two entry boxes in a form to do a sum and update the table. It's to work out a labour rate but though the sum works nicely and always shows in the form, the table field 'Labour_Sum' remains empty. The sum is:

=[Labour_Rate]*[Labour_Cost_Days]

How do I get access to write the results into the table? I need it to write so i can generate invoices which include the results. I've used similar sums for VAT etc in the form and its the same for all of them - the results don't write to the table.

One other thing, why won't the sum work if i get the Rate and and Cost from the table? - i have to use the form fields for it work work or i get a #name error, something like that.

Thanks

Ellie

 

[pbaldy]

If those 2 fields are already being saved to the table, then you probably shouldn't save the result. You would just calculated it on forms/reports/queries when needed.

If you're getting an error when you try to do that, the first thing I'd check is that you don't have controls with the same names as data fields (which is what the wizards will do to you).

 

[MsCrow]

Thanks for replying, how would I make sure the sum results showed up in the report/query as if they're from the table they're of course blank. E.g. a query uses 'Charge@ but there's no data in it as i can only select tables for the query to use not forms

 

[Rich]

as a new field in your query
Charge:[Labour_Rate]*[Labour_Cost_Days]

 

[MsCrow]

oh dear, i can't get it to work - i'm new access user, kind of. when i run the Estimate or Invoice queries from the frm_Units you'll see the error. i would like to be able to save all the sum fields to the table but does that mean they're not changable?

 

[pbaldy]

This works:

Charges: [tbl_jobs].[Labour_Rate]*[tbl_jobs].[Labour_Cost_Days]

That's one reason you don't save the calculated amount; no need to update it if one of the other amounts changes.

 

[MsCrow]

thanks for replying

where would i put that code? in the query i can only add tbl items

edited to add
DUH, i've done it

 

[pbaldy]

Just add it to the query, like this:

 

[MsCrow]

ugh, still a bit stuck with the other two. Following your advice I've done:

Total_VAT: [tbl_Jobs].[Charge]/100*17.5

and when running the query, they're blank

 

[pbaldy]

It looks like it's because there are Null values in some or all of those fields. Use the Nz() function to replace Nulls with 0.

Total_Invoice_Charge: Nz([tbl_Jobs].[Materials_One_Cost],0)+...

You may also want to look into normalizing that table. The various materials_one, two, etc fields look like repeating fields, which are generally not a good idea. Normally you'd split that off into another table, related back to the main table by JobID.

 

[MsCrow]

i realise that, and would have done so but couldn't work out the relationships and got into a bit of a tangle. Solved the one i edited out - sorry hadn't realised you replied - by:

Charge: tbl_Jobs.Materials_One_Cost+tbl_Jobs.Materials_Two_Cost+tbl_Jobs.Materials_Three_Cost+tbl_Jobs.Materials_Four_Cost+tbl_Jobs.Materials_Five_Cost+
tbl_jobs.Labour_Rate*tbl_jobs.Labour_Cost_Days+tbl_Jobs.Hire_Cost

I'm now stuck on VAT and Total invoice:

Total_VAT: tbl_Jobs.Charge/100*17.5

Total_Invoice_Charge: [tbl_Jobs].[Charge]+[tbl_Jobs].[Total_VAT]

Thanks, Ellie

 

[pbaldy]

In the sample, the charge field is empty (Null), so you'd have to account for that with the Nz() function. If that's the field that was to hold the calculated value, you can either refer to the previously calculated field or repeat the calculation, like:

Total_VAT: ([tbl_jobs].[Labour_Rate]*[tbl_jobs].[Labour_Cost_Days])/100*17.5

 

[MsCrow]

ahhh ok then, makes for some long sums though! especially wen it comes to the total invoice charge one. is there ahy esier way? How do you mean by refer to the previously calculated field - would that make the sum shorter?

Ellie

 

[pbaldy]

If you have the field calculating charges:

Charges: [tbl_jobs].[Labour_Rate]*[tbl_jobs].[Labour_Cost_Days]

you can have another that uses it:

VAT: [Charges]/100*17.5

 

[Rich]

VAT: [Charges]*.175

 

[MsCrow]

you wonderful wonderful person, you've assured me a decent sleep tonight Thank you so much.

Ellie