problem getting expression syntax right

[aussie_user]

Hi,

I am building a database and would like to create a field in a query that totals several other fields. I haven't done any calculations before but understand that they can be done and the best place to do them is in a query. I did a search in this forum but could not locate a problem where there was an expression that just involved addition.

I have separate fields to track payments and would like to have a field that calculates the Payment To Date.

I tried

AmtPaidToDate: Sum([FirstPayment]+[SecondPayment]+[ThirdPayment]+[FourthPayment]+[FifthPayment]+[SixthPayment])

When I tried to run the query it said " You tried to execute a query that does not include the specified expression "ProjectID" as part of an aggregate function.

ProjectID is the Key Field in most of my tables and it is included in the query.

Can someone let me know how I would fit the ProjectID field into my expression?

Thanks.

 

[Pat Hartman]

Sum() is used to add values from multiple rows. If all you want to do is add several columns, you don't need it. You will however need to use the Nz() function if there is any chance that one of the fields might be null.

AmtPaidToDate: Nz([FirstPayment]) + Nz([SecondPayment]) +Nz([ThirdPayment]) + Nz([FourthPayment]) + Nz([FifthPayment])+Nz([SixthPayment])

 

[aussie_user]

thanks

Pat - Thanks very much for the reply

There are some zero balances as not all projects have the same amount of payments.

I tried the expression and it works fine.