control field

[nicwnacw]

I can't get my database to do a Sum of all product trasacions - I have used formulae layouts etc from Access templates but they don't work. My knowledge is limited any help gratefully received d/b attached

 

[Jack Cowley]

Change the control source in the Subtotal control on the subform to:

=nz(Sum([#sold]*[SalesPrice]))

Change the control source of the subtotal contron on the main form to:

=[Forms]![Sales Form Basic]![transaction subform Subform].Form.Subtotal

Do not use the "#" character in the name of a control or field.

hth,
Jack

 

[nicwnacw]

Control Field

Brilliant Thank you very much. I have one more little problem now, I have to calculate and add VAT to all sales and then deduct payments. I have this all working fine, BUT some calculations result in a -£0.01 balance. I assume that this has something to do with the VAT as this is not always an exact figure. I need to 'round' the figures but am not sure where to put 'round' in the formulae or in which control field the VAT or the Balance due. I only get the mi9nus figure after I deduct payments.

 

[Jack Cowley]

You are welcome. I am not sure how you are doing your calculations but you should find the answer to your question here.

hth,
Jack

 

[Pat Hartman]

Jack, don't you mean this:

=Sum(Nz([#sold],0)*Nz([SalesPrice],0))

You need to take care of the nulls at the detail level otherwise a null could result in a total of zero rather than a total of everything except the null value.

 

[Jack Cowley]

Pat -

Laughing out loud at myself. Boy, did I go astray with that answer! Thank you for catching it and setting the record straight...

Jack

 

[nicwnacw]

Thank you for that help I have posted a new thread on reports - having got the form to work I cannot get Access to return a value for Sales Price on the invoice report - this is obviously essential