Getting #Error in query response 1

[mrtauntaun]

Ok, I have a query that subtracts two times and returns me a value. So in the query I have

TotalTime: [Query1].[Time1]-[Query2].[Time2]

However, there is not always a [Query2].[Time2] value. I have the query set up to show all records from Query1, but only those from Query2 which are equal. If there is no Time2, that is when it generates me the #Error.
All I want is If Time2=Null then Time2=0
What is the syntax for that and where do I put it in?

 

[PHV]

TotalTime: [Query1].[Time1]-Nz([Query2].[Time2],0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrtauntaun]

Hi PHV. Unfortunately, after adding that, it still returns #Error

 

[PHV]

Any chance you could post the SQL code of your query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrtauntaun]

SELECT Query1.PrimKey, [Query1].[Time1]-[Query2].[Time2] AS [Total Time]
FROM Query1 LEFT JOIN Query2 ON Query1.PrimKey = Query2.PrimKey;

 

[PHV]

And what about this ?
SELECT Query1.PrimKey, [Query1].[Time1]-Nz([Query2].[Time2],0) AS [Total Time]
FROM Query1 LEFT JOIN Query2 ON Query1.PrimKey = Query2.PrimKey

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrtauntaun]

That's what I had tried previously (sorry, should have pasted both), and it still returned the #Error
Im not sure what Nz is.
I actually solved it a little backwards, the query takes a little longer to run than i'd like, but I can live with it. Bascially I used an IIf statement saying

iif(Time2 is null, Time1, Time1-Time2)

and that does it.
If the Nz statement runs faster than an iif, I would love that, but i am getting results.
Thanks!!!