If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#Error in Query - but not a div by 0 - so what is it?
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 |
#2
|
|||
|
|||
#Error in Query - but not a div by 0 - so what is it?
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) Katie Scott wrote: 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" |
#3
|
|||
|
|||
#Error in Query - but not a div by 0 - so what is it?
Yes indeed!!! Thanks so much. That was driving me crazy!!!
Katie "Bob Barrows" wrote: 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) Katie Scott wrote: 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" |
Thread Tools | |
Display Modes | |
|
|