adding fields in query

[slimjen]

all, using access 2003. have table contains number fields. I am using a query in a subform. I want to add the number fields to place them on a field called total charges. please help. this is what i am placing in the query

 
TOTALCHARGES: DSum([NUM_TRANSCHG],[NUM_FUELSURCHG],[NUM_ACCESCHG],[NUM_OTHERCHG])

its returning a error about wrong # of arguments.
thanks

[Rainlover]

It is not "Best Practise" to store calculated values. The better method is to calculate when you need the value.

Sum is not what I think you want because it returns the Sum of all the values in one field.

You simply need to use +

[slimjen]

ok. i need to use it on a subform. in the query using +; it works but only if all the fields have something in them How can I correct this using the calculated field on a subform that allows any of the fields to be empty. thanks

[alansidman]

Access does not know what to do in a calculation if a field is empty. You need to tell it what to do. This is where the NZ function applies. Here is an explanation of the NZ function and how to employ it.

http://www.techonthenet.com/access/f...dvanced/nz.php

So if you had a field named, Output and you wished to add it to a field named Receipts you would use the following expression
NZ(Output,0) + NZ(Receipts,0)

This tells Access that if there is nothing in either of those fields, use a zero. Now Access can do a calculation

[slimjen]

thanks! this work. can i do the same in vba to use it on a subform since its not good practice to store calculated fields?

[slimjen]

I got it. But I am having a problem with making the field 2 decimals. I have the field properties set at 2 but it still appears 4 instead of 4.00. any ideas

[Rainlover]

Try formating the field as Decimal. (Not tested)

Do this on the Form.