Calculating Fields in a form

[Layward]

-- I am trying to have access 2007 to automatically add up field in a form.
The form is based on Monthly expenditure I have tried using the expression
builder by placing the following format =Sum([Motoring
Expenses]+[Phone/Broadband]+[Postage]+[Office Equip]+[Rail Travel]+[Capital
Equip]+[Subsistence]+[Sundries]) Into a Total field. But it will not work has
any one have any ideas how I can do this. Also I have a problem with entering
A VAT calculator I want to enter 17.5% but the record returns 18% any idea
how I can correct it? Many thanks in anticipation
Layward

 

[fredg]

-- I am trying to have access 2007 to automatically add up field in a form.
The form is based on Monthly expenditure I have tried using the expression
builder by placing the following format =Sum([Motoring
Expenses]+[Phone/Broadband]+[Postage]+[Office Equip]+[Rail Travel]+[Capital
Equip]+[Subsistence]+[Sundries]) Into a Total field. But it will not work has
any one have any ideas how I can do this. Also I have a problem with entering
A VAT calculator I want to enter 17.5% but the record returns 18% any idea
how I can correct it? Many thanks in anticipation
Layward

Words like "will not work" do not offer us any indication of what may
be wrong. Do you get an #error? Or perhaps an incorrect total? or a
blank? And what do you mean by a "Total Field"? Are you trying to save
the sum into a table?

I would suggest you try:
1) Make sure the name of the control is not the same as the name of
any field used in it's control source expression.

2) If any of the field's in the expression has no data (Is Null) the
Sum will not be correct (Null + 5 = Null, not 5)
Use the Nz() function:
=Sum(Nz([Motoring Expenses],0)+Nz([Phone/Broadband],0)+Nz([Postage],0)
+Nz(etc....))
Look it up in VBA help.

3) The above calculation should only "Display" on the form, it should
not be stored in any table. Any time you need the total, re-calculate
it.

4) Regarding the VAT rounding up, I would suspect the datatype of the
VAT field is Number with a Field Size of Long Integer or Integer.
By definition, an Integer is a whole number and cannot have a decimal
value. Either change the VAT field's datatype to Currency datatype or
change it's Field Size to Double.

 

[Layward]

Hi Thanks for your help, However I managed to sort the problem out by
creating a query which allowed me to add calculate with out any problem. .
As for the Vat your suggestion worked. Many Thanks--
Layward

-- I am trying to have access 2007 to automatically add up field in a form.
The form is based on Monthly expenditure I have tried using the expression
builder by placing the following format =Sum([Motoring
Expenses]+[Phone/Broadband]+[Postage]+[Office Equip]+[Rail Travel]+[Capital
Equip]+[Subsistence]+[Sundries]) Into a Total field. But it will not work has
any one have any ideas how I can do this. Also I have a problem with entering
A VAT calculator I want to enter 17.5% but the record returns 18% any idea
how I can correct it? Many thanks in anticipation
Layward

Words like "will not work" do not offer us any indication of what may
be wrong. Do you get an #error? Or perhaps an incorrect total? or a
blank? And what do you mean by a "Total Field"? Are you trying to save
the sum into a table?

I would suggest you try:
1) Make sure the name of the control is not the same as the name of
any field used in it's control source expression.

2) If any of the field's in the expression has no data (Is Null) the
Sum will not be correct (Null + 5 = Null, not 5)
Use the Nz() function:
=Sum(Nz([Motoring Expenses],0)+Nz([Phone/Broadband],0)+Nz([Postage],0)
+Nz(etc....))
Look it up in VBA help.

3) The above calculation should only "Display" on the form, it should
not be stored in any table. Any time you need the total, re-calculate
it.

4) Regarding the VAT rounding up, I would suspect the datatype of the
VAT field is Number with a Field Size of Long Integer or Integer.
By definition, an Integer is a whole number and cannot have a decimal
value. Either change the VAT field's datatype to Currency datatype or
change it's Field Size to Double.