Null value prevents calculation

[Yellowstone Valley Tree Surgeons]

I have a table named Jobs that contains a [Total] field where the cost for
the job is entered. I have another table named Payments that contains a
[Payment] field where payments are entered. I have a one-to-many
relationship setup so there can be multiple payments for each job. When I
run a query to see which jobs are not paid, the ones with no payments at all
do not show up because there has been no entry made yet. The field has a
null value. How can I get the query to subtract a zero payment from the job
when there is no payment yet?

 

[Rob Parker]

You've actually got two questions in there.

For the first, to see the jobs which have no payments, you need to change
the type of join between Jobs and Payments. Right-click on the join line,
select JoinProperties, then select the option to show ALL records from Jobs
and only those records from Payments where the joined fields are equal.

If you still have your second problem - subtracting a zero payment when the
payment is null - then use the Nz function to convert a null value to zero
(you can actually use it to convert the null to anything you want). The
syntax is:
nz([Payment],0)
Use this instead of [Payment] in your calculated expression. Be aware that
the result of expressions which contain Null will be null (except when the &
operator is used for concatenation, when a null is treated as a zero-length
string).

HTH,

Rob

 

[Yellowstone Valley Tree Surgeons]

Thank you thank you! That worked! I changed the relationship which fixed the
problem with non-existant records of Payments. Then the Nz function worked.
I had tried Nz already, but it didn't work because the join was wrong.