Solved: Sum Multiple Fields

[brorick]

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.

[WillR]

I usually use the nz function.

see this link

http://msdn.microsoft.com/library/de...HV05186465.asp

[brorick]

Thank you for your response. I will look into it.

[OBP]

brorick, are you trying to do this to put a total in a field on the form or a report?

[brorick]

On a form.

[OBP]

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]

Thank you OBP. I appreciate the response. I will definately try this method. Have a great day.

[OBP]

brorick, did it solve your problem?