Summing in Query

[Unicon]

Dear all,

Please advise re attachment as I want to show total values in Total earnings and write down the feild with (+) but it is not working. It is working only in the condition if whole feild is filled up with value. Might be I am doing wrong.

Help me out.

 

[CBrighton]

Nz([FieldName],0)

This will replace any null values with zero, which should allow the calculated field to work correctly.

Use the above code for each field reference within your Total Earnings field.

 

[John Big Booty]

Perhaps a Totals Query is what you are looking for.

 

[Unicon]

Thanks CBrighton,

No idea where I can put that code.

Please advise.

 

[CBrighton]

In your calculated field, instead of just adding up the fields add the fields within their own Nz functions:

Total Earnings: (Nz([Basic Salary],0)+Nz([Food],0)+Nz([Normal OT],0)+Nz([Weekly  Off OT],0)+Nz([Monthly Performance],0)+Nz([Site Allowance],0))

Forgive any typos in field names, screen-print is bad quality or my browser is stretching it out of shape.

 

[Unicon]

Received below message,

The expression you entered contains invalid syntax
You may have entered and operand without an operator

 

[CBrighton]

See attachment.

qryQuery has 5 currency fields, Total1 field uses a straight sum, Total2 uses a sum with Nz.

 

[Unicon]

Done

Thanks CBrighton.

 

[CBrighton]

I assume you found the two missing brackets in my example (or just rewrote it using the correct number of brackets).

I have edited my post in case anyone else finds this thread when looking for a solution to the same problem.

 

[Unicon]

True there is two missing bracket which I have noticed later only.

Anyway thanks a lot.