#Error in Query - but not a div by 0 - so what is it?

[Katie Scott]

Hello to all,

I am puzzling over a problem I'm having with a query that used to work and
now gives me a #Error. Even when I simplify it - it is a problem and I am
pulling my hair out trying to understand what's going on. Probably something
simple that I'm missing - but what???

If I try a comparison of two fields using IIF I get #Error everywhere.
ckSpike: IIf([SourceResult]>[Expected Value],1,0)
returns nothing but #Errors.

But if I just report the difference between the two values it's okay.
ckSpike2: [SourceResult]-[Expected Value]
returns
-787.6
-630
50
etc

?? What's wrong with comparing the two values with a > sign? I do it all
the time elsewhere - and it used to work here....

Thanks for all your help!!!

Katie

 

[Bob Barrows]

One of them must contain a Null. Since they both appear to be numeric, use
Nz to force the nulls to 0.
IIf(Nz([SourceResult],0)>Nz([Expected Value],0),1,0)

Hello to all,

I am puzzling over a problem I'm having with a query that used to
work and now gives me a #Error. Even when I simplify it - it is a
problem and I am pulling my hair out trying to understand what's
going on. Probably something simple that I'm missing - but what???

If I try a comparison of two fields using IIF I get #Error everywhere.
ckSpike: IIf([SourceResult]>[Expected Value],1,0)
returns nothing but #Errors.

But if I just report the difference between the two values it's okay.
ckSpike2: [SourceResult]-[Expected Value]
returns
-787.6
-630
50
etc

?? What's wrong with comparing the two values with a > sign? I do it
all the time elsewhere - and it used to work here....

Thanks for all your help!!!

Katie

 

[Katie Scott]

Yes indeed!!! Thanks so much. That was driving me crazy!!!

Katie

One of them must contain a Null. Since they both appear to be numeric, use
Nz to force the nulls to 0.
IIf(Nz([SourceResult],0)>Nz([Expected Value],0),1,0)

Hello to all,

I am puzzling over a problem I'm having with a query that used to
work and now gives me a #Error. Even when I simplify it - it is a
problem and I am pulling my hair out trying to understand what's
going on. Probably something simple that I'm missing - but what???

If I try a comparison of two fields using IIF I get #Error everywhere.
ckSpike: IIf([SourceResult]>[Expected Value],1,0)
returns nothing but #Errors.

But if I just report the difference between the two values it's okay.
ckSpike2: [SourceResult]-[Expected Value]
returns
-787.6
-630
50
etc

?? What's wrong with comparing the two values with a > sign? I do it
all the time elsewhere - and it used to work here....

Thanks for all your help!!!

Katie

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"