PDA

View Full Version : Solved: Sum Multiple Fields



brorick
04-01-2005, 07:05 AM
I have been unsuccessful using sum and dsum in access. I have twelve fields in my form, january through december. I want to create a running total of the entered amounts. I think the problem is due to the fact only a few months contain data, such as january through march. The other fields are blank. I also attempted to do this in a query and no luck.

My field names in the form have simple labels, such as jan, feb, mar, apr, may, jun, july, aug, sept, oct, nov, dec, total. Does anyone have a simple and quick solution. Any help is greatly appreciated. Thank you in advance. http://www.mrexcel.com/board2/images/smiles/icon_eek.gif

WillR
04-01-2005, 08:14 AM
I usually use the nz function.

see this link

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acfctNZ_HV05186465.asp

brorick
04-01-2005, 08:16 AM
Thank you for your response. I will look into it.

OBP
04-12-2005, 11:05 AM
brorick, are you trying to do this to put a total in a field on the form or a report?

brorick
04-12-2005, 12:12 PM
On a form.

OBP
04-13-2005, 02:06 AM
As far as I can see you have two options to overcome your current problem.
1. remove the Null entries from the table with a global replace and replace the nulls with zeros. Add zero as the default entry on the Form for future records. Your calculations should then work OK.
2. Manipulate the current data including the Null values either at the query level or at the Form level with VBA code.
To do so at the query level the simplest way would be to add twelve new fields to the query one for each month and use this formula Expr1: IIf(IsNull([value 1]),0,[value 1]) in each column changing the field name for each one. Now you can simply add the new field values together, sum them or whatever.

brorick
04-13-2005, 05:11 AM
Thank you OBP. I appreciate the response. I will definately try this method. Have a great day.

OBP
04-23-2005, 01:55 AM
brorick, did it solve your problem?