Re: Null value prevents calculation
- From: "Rob Parker" <CUT_THIS robpparker at optusnet dot com dot au>
- Date: Fri, 5 May 2006 09:33:23 +1000
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" <yvts@xxxxxxxxxxx> wrote in message
news:Oueoyz8bGHA.3824@xxxxxxxxxxxxxxxxxxxxxxx
I have a table named Jobs that contains a [Total] field where the cost forall
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
do not show up because there has been no entry made yet. The field has ajob
null value. How can I get the query to subtract a zero payment from the
when there is no payment yet?
.
- References:
- Null value prevents calculation
- From: Yellowstone Valley Tree Surgeons
- Null value prevents calculation
- Prev by Date: Re: Question on doing a date calculation in MS Access Query
- Next by Date: Re: Joining two tables
- Previous by thread: Null value prevents calculation
- Next by thread: Re: Null value prevents calculation
- Index(es):
Relevant Pages
|
