Query with Null table

[Chris F via AccessMonster.com]

good morning-

I have two tables: t2PTaskTakeoff which contains three fields PTaskTakeoffID,
Designation and Length, and the table t3SRActivityTakeoffWork with three
fields SRActivityTakeoffID, Length, and PTaskTakeoffID.

The idea is that t2PTaskTakeoff records the design length of pipe. The
second table is updated each time a crew is requested to build a portion of
the pipe and the built length is recorded. I would like to design a query
that will show the Pipe Name (Designation) and the length of pipe left. My
problem is when the query is initially run (prior to a crew building any
portion of the pipe) my query returns a null value for the field "LengthLeft".
How would I designate in this formula that if the record is null, return the
total length of pipe?
My query is below:

SELECT t2PTaskTakeoff.Designation, ([t2PTaskTakeoff].[Length]-
[t3SRActivityTakeoffWork].[Length]) AS LengthLeft
FROM t2PTaskTakeoff LEFT JOIN t3SRActivityTakeoffWork ON t2PTaskTakeoff.
PTaskTakeoffID = t3SRActivityTakeoffWork.PTaskTakeoffID;

Any help would be greatly appreciated

Thanks,
Chris F

 

[Dale Fye]

Use the NZ( ) function to convert the NULL value in the
[t3SRActivityTakeoffWork].Length field to a zero.

SELECT t2PTaskTakeoff.Designation, [t2PTaskTakeoff].[Length]-
NZ([t3SRActivityTakeoffWork].[Length],0) AS LengthLeft
FROM t2PTaskTakeoff
LEFT JOIN t3SRActivityTakeoffWork
ON t2PTaskTakeoff.PTaskTakeoffID = t3SRActivityTakeoffWork.PTaskTakeoffID;

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[tobesurveyor via AccessMonster.com]

Thanks Dale, Works Perfectly!

Use the NZ( ) function to convert the NULL value in the
[t3SRActivityTakeoffWork].Length field to a zero.

SELECT t2PTaskTakeoff.Designation, [t2PTaskTakeoff].[Length]-
NZ([t3SRActivityTakeoffWork].[Length],0) AS LengthLeft
FROM t2PTaskTakeoff
LEFT JOIN t3SRActivityTakeoffWork
ON t2PTaskTakeoff.PTaskTakeoffID = t3SRActivityTakeoffWork.PTaskTakeoffID;

good morning-

[quoted text clipped - 21 lines]

Thanks,
Chris F