Re: "You canceled the previous operation."

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi John,

You hit it right on the head with the zero length string and null value point.
I remembered the table of error values created when I imported this table
from excel. I looked up any errors in the motcode field, and sure enough,
there were 76 of them, the exact same number of records missing from my
search results.

I tried the code you wrote the previous time, and it looks like it should
work, but everytime I run the query I get pop-up boxes (twice) asking for the
value of [Forms]![Case-BasedQuery]![motcode]. Can you really join two sets of
values with OR? I ask because I tried to set the criteria to accept either
the values 1 or 2, like so:
IIf([Forms]![Case-Based Query]![motcode] Is Not Null,[Forms]![Case-Based
Query]![motcode],1 or 2)
.... but it just returned no results. Tried '1 & 2' but that just gives me
results where motcode=12.

I've just now tried to use Nz as well, but am not having much luck there
either. Using the fragment of code you gave yields the same number of results,
and I think I can see that's because Nz never evaluates [combined simple].
motcode as null because [combined simple].motcode contains all possible
values of motcode. Does that make sense?

Even if Nz evaluates to null I don't think it'd help me in this case as all I
could do is set it to another value, say -999, that doesn't exist in the
table. Therefore, I'd still not get the missing results as it'd still be
ignoring the null values in the table.

I'm thinking maybe I should run a sub that replaces all the null values in
the table with some dummy value. Would rather find some other way though as
it just complicates things and also leaves a bunch of ugly '-999's everywhere

Thanks again for helping. Really appreciate it.

Steve




John Spencer (MVP) wrote:
>Whoops! Change the last line of the first paragraph to read
>
> That's why you can NOT use "Something = Null" for a comparision, but can use
>the IS NULL operator - "Something Is Null".
>
>> As a guess, [Combined Simple].CCR contains a zero length string and not a null
>> value. To humans they look the same, to the computer they are not the same.
>[quoted text clipped - 58 lines]
>> > >WHERE (MotCode = [Forms]![Case-Based Query]![motcode] OR
>> > >[Forms]![Case-BasedQuery]![motcode] is Null) ...


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200508/1
.