NZ Request

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

any ideas:

I'm trying to work out a balance for each account in a
seriers of querys that are based on four tables.
Main tbl has the ClientID
Payment has the PaymentID linked to the ClientID
Property tbl links PropertyID to ClientID (1 to many)
Service tbl links ServiceID to PropertyID and each
service has a cost. Each cost is either rendered or not
rendered (Yes/No box)

Each service has a fee but the billing goes to the client
(who may have many properties each with many services).

I've created two queries one to sum the payments and the
other to sum the rendered costs

What I want is a (sum of payment)-(sum of rendered costs)
but if I create the expression
Balance: (NZ([sumofpayment],0))-(NZ([sumofcost],0))
All calculations with null values on one side or another
don't show at all. Can anyone help?
 
Ian

What happens if you apply the Nz() function in the earlier queries?

Jeff Boyce
<Access MVP>
 
I don't really want to show all the null values in the
previous.

For instance:
There are 100 total clients
10 have had a "cost" applied (service) AND made
a "payment" - there balances show
10 have only had a "cost" - in the query to show sum of
costs by ID they all show but if I try to calculate a
balance Balance: [sumofpayments]-[sumofcosts] they don't
show because the sumofpayments in null
-----Original Message-----
Ian

What happens if you apply the Nz() function in the earlier queries?

Jeff Boyce
<Access MVP>

any ideas:

I'm trying to work out a balance for each account in a
seriers of querys that are based on four tables.
Main tbl has the ClientID
Payment has the PaymentID linked to the ClientID
Property tbl links PropertyID to ClientID (1 to many)
Service tbl links ServiceID to PropertyID and each
service has a cost. Each cost is either rendered or not
rendered (Yes/No box)

Each service has a fee but the billing goes to the client
(who may have many properties each with many services).

I've created two queries one to sum the payments and the
other to sum the rendered costs

What I want is a (sum of payment)-(sum of rendered costs)
but if I create the expression
Balance: (NZ([sumofpayment],0))-(NZ([sumofcost],0))
All calculations with null values on one side or another
don't show at all. Can anyone help?
Click to expand...

.
Click to expand...
 
Tried NZ anyway in the supporting queries and it still
didn't help.
-----Original Message-----
Ian

What happens if you apply the Nz() function in the earlier queries?

Jeff Boyce
<Access MVP>

any ideas:

I'm trying to work out a balance for each account in a
seriers of querys that are based on four tables.
Main tbl has the ClientID
Payment has the PaymentID linked to the ClientID
Property tbl links PropertyID to ClientID (1 to many)
Service tbl links ServiceID to PropertyID and each
service has a cost. Each cost is either rendered or not
rendered (Yes/No box)

Each service has a fee but the billing goes to the client
(who may have many properties each with many services).

I've created two queries one to sum the payments and the
other to sum the rendered costs

What I want is a (sum of payment)-(sum of rendered costs)
but if I create the expression
Balance: (NZ([sumofpayment],0))-(NZ([sumofcost],0))
All calculations with null values on one side or another
don't show at all. Can anyone help?
Click to expand...

.
Click to expand...
 
Back
Top