sum not working if field is 0

  • Thread starter Thread starter Meg
  • Start date Start date
M

Meg

I have a query which sums fields in two tables.

The first table is checks and it sums checkswritten by
customer.
The second table is wires and it sums outgoingwires by
customer.
The query is Tranferedamount and the formula is
Transferededamount: [Sum Of checkswritten]+[Sum Of
outgoingwires].

It works fine when both checkswritten and outgoingwires
are not equal to 0, but if one if them is 0, then the
query doesn't seem to work.

Can someone point out what I am doing wrong?

Thanks so much,

Meg
 
Try
Transferededamount: Nz([Sum Of checkswritten],0)+Nz([Sum Of
outgoingwires],0)
Click to expand...

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a query which sums fields in two tables.

The first table is checks and it sums checkswritten by
customer.
The second table is wires and it sums outgoingwires by
customer.
The query is Tranferedamount and the formula is
Transferededamount: [Sum Of checkswritten]+[Sum Of
outgoingwires].

It works fine when both checkswritten and outgoingwires
are not equal to 0, but if one if them is 0, then the
query doesn't seem to work.

Can someone point out what I am doing wrong?

Thanks so much,

Meg
.
Click to expand...
 
It is probably the Null that is messing things up. Try:
Transferededamount: Nz([Sum Of checkswritten],0) + Nz([Sum Of
outgoingwires],0)

It may also be a good idea to convert the result back to Currency:
Transferededamount: CCur(Nz([Sum Of checkswritten],0) + Nz([Sum Of
outgoingwires],0))
 
Yes, that worked! Thanks!
-----Original Message-----
It is probably the Null that is messing things up. Try:
Transferededamount: Nz([Sum Of checkswritten],0) + Nz ([Sum Of
outgoingwires],0)

It may also be a good idea to convert the result back to Currency:
Transferededamount: CCur(Nz([Sum Of checkswritten],0) + Nz([Sum Of
outgoingwires],0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Meg said:
I have a query which sums fields in two tables.

The first table is checks and it sums checkswritten by
customer.
The second table is wires and it sums outgoingwires by
customer.
The query is Tranferedamount and the formula is
Transferededamount: [Sum Of checkswritten]+[Sum Of
outgoingwires].

It works fine when both checkswritten and outgoingwires
are not equal to 0, but if one if them is 0, then the
query doesn't seem to work.

Can someone point out what I am doing wrong?

Thanks so much,

Meg
Click to expand...


.
Click to expand...
 
Back
Top