Sum (Sub-Total) QRY Help

[Marbinski]

I have a query that has a field that sums the total of 3 other fields. My problem is whenever one of the 3 values is null, the subtotal does not work, but when all fields have a value it works perfectly fine. Is there a way to properly code this so that the field(s) that are null are given the value of 0 (zero) so that the subtotal can work?

Here is what I already have:
Subtotal:[field1]+[field2]+[field3]


Any suggestions or help would be very much appreciated. Thank you in advance.

 

[samonwalkabout]

Create an update qry that updates all the NULL values to 0 and run this. This will resolve any existing nulls to Zero.

Change the default value of the field to Zero.

You can then either make sure that people can't enter null vaules in your forms or set this as a validation rule for the feild in question or just run the update qry on a form event so that the null vaules are changed to zero's.

 

[Marbinski]

Thanks for the suggestion, but I just found a way to do it by code.
But I did use that default property set to 0. =)

I've been playing with this as a field in my query:

Field1Value: IIf([Field1] Is Null,0,[Field1])

I am going to do this with all the the three fields then sum from there.

Subtotal:[Field1Value]+[Field2Value]+[Field3Value]

Well I hope this helps someone...if they ever run into it.

 

[samonwalkabout]

Where are these Null values coming from?

Are you using forms?

If so on the afterupdate of a field use

if isnull(yourfield) then
me.yourfield = 0
end if

this will stop any nulls.
Or set the field validation rule to IS NOT NULL

For your exisitng null vaules you can manually change them to 0 but really you should get into update qrys they save a lot of time. Just select the qry time update..add the table.. choose the field.. put 0 in the update to .....and set Null as teh critera... if you are worried about screwing your data make a copy of the table 1st and try the query on that or make a copy of the whole db..which is always a good idea before you change anything

 

[Marbinski]

That is exactly what I wanted....and works great, my solution was a little off tangent from being efficient. Thank you for all your help.