#Error in my Qry 2

[knuckelhead]

I am getting #error on this Qry calculated field.

The Cleanhours1 comes from Qry1 that seems to work fine. However, Qry1 which has these CleanHours1 may not have a matching record in Qry2. Qry2 is the primary join Qry where i want all Qry2 Sales records.

In Qry3, i Join Qry1 and Qry2. The CleanHours3 are in this Qry3. The answer will be numeric. the following is in Qry3:

CleanHours3: IIf(nz([CleanHours1])="",0,nz([CleanHours1]))

So, if I have a Qry2 sales record which has no Qry1 CleanHours1, I was hoping that Qry3 Cleanhours3 would show 0 or zero hours.

Instead, i get #error. If I run Qry1 or Qry2 separately, I do not get a #error condition. So it #error is happening in my Qry3 final Qry.

Any ideas how to get rid of this #error?

 

[PHV]

And what about something like this ?
CleanHours3: [tt]Val([CleanHours1] & '')[/tt]

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

 

[Golom]

If [CleanHours1] is a numeric field and contains NULL then NZ([CleanHours1]) will return zero ... not an empty string. You probably want

IIF(NZ([CleanHours1]) = 0, ...

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[knuckelhead]

You both did it! It woiks Moe!
I used your ideas. I was struggling to make an IIF statement and then saw that last good remark. together, it now works. the #error goes away.

Here is my real calculated field names etc.

SetupCleaningMH3: IIf(nz([BatchsizeProduction])=" " Or nz([PackID])=" ",0,nz([SetupCleaningMH2]))

Please yell, if you see room for improvement.
thanks so much. i will donate to the club in your names again.

Knucklehead Smith