sum / total

[dbnovice]

Hello there
im having trouble summing a number of fields that contain a value of £0.00 or blank for example i have five fields on a form £2.50 £0.00 £3.00 £ £4.00 if all fields have a value then the correct total is displayed i have tried =([price1]+[price2]) ect or sum which does work but not if £0.00 or £ are used obviously the result i am after is total £9.50 can anyone help i want to be able to sum or add all fields to give a total including numbers £0.00 and blanks please no code because i wouldn't know what to do with it just keep it as simple as possible i intend to write the formula in the totals field not in the footer i would really appreciate your sugestions
many thanks Richard

 

[Rich]

Do you mean 5 fields or 5 records, if it's the first then Access isn't a spreadsheet, you'll need to normalise your data

 

[rwwilliamson]

Presuming you have a table that contains the 5 fields, and each is formatted as "currency", then the simplest way is to populate the form from a query that will assemble the desired total. If the table is created correctly in the beginning, a null (blank) record isn't possible, since each field should have a default of 0, thus the formula will always add correctly. A null is not processed the same as a value and will give the problem you describe.

create query with source as tablename with field1,field2 . .. and then create the total by typing in the following in the "Field" row of a blank column:
Total: [tablename].[field1]+[tablename].[field2]+[tablename].[field3]+[tablename].[field4]+[tablename].[field5]

Hilite this column and select "Properties." In Format, choose format you want displayed. Save query.

Create your form using the data source as the query. Note that Total isn't saved anywhere and is only created as the form is opened.

Other options are to create the total in the form using VBA code, but you have a desire to avoid that.

Hope this helps.