"You canceled the previous operation."

I've got a form with some comboboxes, five checkboxs and a command button on
it. The command button simply launches a query.

I'm getting some very strange behaviour:
When I check the first checkbox the query runs fine. However, checking any of
the subsubsequent four checkboxes throws up a "You canceled the previous
operation." error. I read around these forums but wasn't able to find
anything that solves the problem. I've compact & repaired, decompiled and
created and imported into a new database, but still get the same error.

I fiddled around a little more and found that if I checked one of the problem
checkboxes but then instead of hitting the command button, opened the query
from the queries menu, I got this error message instead:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

I'll admit the query is kinda ugly, but I'm really not sure how I can
simplify it. I've listed the query below. The five checkboxes a
Forms]![Case-Based Query]![viet_camp]
Forms]![Case-Based Query]![c_rob_bur_c]
Forms]![Case-Based Query]![vic_nonc]
Forms]![Case-Based Query]![offchi]
Forms]![Case-Based Query]![sex_as_c]
and the rest are comboboxes

As for the IIf monkey business, it basically says if the the combobox isn't
empty then take that value, else take all the possible values that field can
have (and also null)

================================================== ===================
SELECT
[combined simple].org_case_no,
[combined simple].ccr,
[combined simple].c_year,
[combined simple].c_18district,
[combined simple].motcode,
[combined simple].vsex,
[combined simple].vage,
[combined simple].offsexcode,
[combined simple].offage,
[combined simple].relacode,
[combined simple].viet_camp,
[combined simple].c_rob_bur_c,
[combined simple].vic_nonc,
[combined simple].offchi,
[combined simple].sex_as_c,
[combined simple].weafirea,
[combined simple].ID
FROM [combined simple]
WHERE
((([combined simple].org_case_no)=IIf([Forms]![Case-Based Query]![org_case_no]
Is Not Null,[Forms]![Case-Based Query]![org_case_no],([combined simple].
[org_case_no]) & Null))
AND (([combined simple].ccr)=IIf([Forms]![Case-Based Query]![ccr] Is Not Null,
[Forms]![Case-Based Query]![ccr],([combined simple].[ccr]) & Null))
AND (([combined simple].c_year)=IIf([Forms]![Case-Based Query]![c_year] Is
Not Null,[Forms]![Case-Based Query]![c_year],([combined simple].[c_year]) &
Null))
AND (([combined simple].c_18district)=IIf([Forms]![Case-Based Query]!
[c_18district] Is Not Null,[Forms]![Case-Based Query]![c_18district],(
[combined simple].[c_18district]) & Null))
AND (([combined simple].motcode)=IIf([Forms]![Case-Based Query]![motcode] Is
Not Null,[Forms]![Case-Based Query]![motcode],([combined simple].[motcode]) &
Null))
AND (([combined simple].vsex)=IIf([Forms]![Case-Based Query]![vsex] Is Not
Null,[Forms]![Case-Based Query]![vsex],([combined simple].[vsex]) & Null))
AND (([combined simple].vage)=IIf([Forms]![Case-Based Query]![vage] Is Not
Null,[Forms]![Case-Based Query]![vage],([combined simple].[vage]) & Null))
AND (([combined simple].offsexcode)=IIf([Forms]![Case-Based Query]!
[offsexcode] Is Not Null,[Forms]![Case-Based Query]![offsexcode],([combined
simple].[offsexcode]) & Null))
AND (([combined simple].offage)=IIf([Forms]![Case-Based Query]![offage] Is
Not Null,[Forms]![Case-Based Query]![offage],([combined simple].[offage]) &
Null))
AND (([combined simple].relacode)=IIf([Forms]![Case-Based Query]![relacode]
Is Not Null,[Forms]![Case-Based Query]![relacode],([combined simple].
[relacode]) & Null))
AND (([combined simple].viet_camp)=IIf([Forms]![Case-Based Query]![viet_camp]
=-1,'1',([combined simple].[viet_camp]) & Null)) AND (([combined simple].
c_rob_bur_c)=IIf([Forms]![Case-Based Query]![c_rob_bur_c]=-1,'1',([combined
simple].[c_rob_bur_c]) & Null))
AND (([combined simple].vic_nonc)=IIf([Forms]![Case-Based Query]![vic_nonc]=-
1,'1',([combined simple].[vic_nonc]) & Null)) AND (([combined simple].offchi)
=IIf([Forms]![Case-Based Query]![offchi]=-1,'1',([combined simple].[offchi])
& Null))
AND (([combined simple].sex_as_c)=IIf([Forms]![Case-Based Query]![sex_as_c]=-
1,'1',([combined simple].[sex_as_c]) & Null))
AND (([combined simple].weafirea)=IIf([Forms]![Case-Based Query]![weafirea]
Is Not Null,[Forms]![Case-Based Query]![weafirea],([combined simple].
[weafirea]) & Null)));


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200508/1

Oh and I forgot to mention that there's something also very strange going on.
When I do the search it never returns the records with c_year beyond 1999, so
I never get any records from this century. What could cause something like
this?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200508/1

I, sir, am an idiot. Turns out that in lines like this I just needed to get
rid of the quotes around the 1:
IIf([Forms]![Case-Based Query]![c_rob_bur_c]=-1,'1',([combined
simple].[c_rob_bur_c]) & Null))

So, I've fixed that, but now the problem I get is that some of the records in
the database won't show up when I do my search. I'm pretty sure it's because
they contain null values, but I'm not sure what to do about it. Any
suggestions?

Also, still having problems with the bug that doesn't show records beyond
1999


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200508/1

Okay, I went through my query again and found out what was causing some
records not to show up in the query.

Here's where I'm having a problem:
IIf([Forms]![Case-Based Query]![motcode] Is Not Null,[Forms]![Case-Based
Query]![motcode],[combined simple].[motcode] & Null)

What I want is the IIf to test if the combobox is empty. If so, take that
value, else take any possible values from [combined simple].[motcode].
However, the query takes every value except for Null values, so any records
with a null value for motcode doesn't appear in the results. Is there any way
to force the query to accept null values as well?

Steven L


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200508/1

If you are using the query grid then enter the following all in one criteria
cell. Access will rearrange this when you save and close the query.

Field:[motcode]
Criteria: [Forms]![Case-Based Query]![motcode] OR
[Forms]![Case-BasedQuery]![motcode] is Null

If you aren't using the query grid to build the query then

WHERE (MotCode = [Forms]![Case-Based Query]![motcode] OR
[Forms]![Case-BasedQuery]![motcode] is Null) ...

"Steven L via AccessMonster.com" wrote:

Okay, I went through my query again and found out what was causing some
records not to show up in the query.

Here's where I'm having a problem:
IIf([Forms]![Case-Based Query]![motcode] Is Not Null,[Forms]![Case-Based
Query]![motcode],[combined simple].[motcode] & Null)

What I want is the IIf to test if the combobox is empty. If so, take that
value, else take any possible values from [combined simple].[motcode].
However, the query takes every value except for Null values, so any records
with a null value for motcode doesn't appear in the results. Is there any way
to force the query to accept null values as well?

Steven L

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200508/1

Hi, thanks for the reply and the help.

Fortunately or unfortunately, I've found out that the null value has little
to do with what's going wrong with the query.

I took apart the query to see what was going wrong and found out that just
using the below as a criteria gave perfect results:
======================
([combined simple].ccr)=IIf([Forms]![Case-Based Query]![ccr] Is Not Null,
[Forms]![Case-Based Query]![ccr], [combined simple].[ccr])
======================


But then when I added this criteria below all records that had a null value
for motcode didn't show up.
======================
([combined simple].motcode)=IIf([Forms]![Case-Based Query]![motcode] Is
Not Null,[Forms]![Case-Based Query]![motcode],[combined simple].[motcode])
======================

I can't see why this would be, as both criteria are exactly the same, and the
ccr field contains several null values as well.

I thought it may have something to do with the two fields containing
different data types (ccr contains text, motcode contains doubles), so I
changed motcode into text and all it's properties so that they were the same
as ccr, but yielded the same results.

Very confused. Hope you can help.

Steve




John Spencer (MVP) wrote:
If you are using the query grid then enter the following all in one criteria
cell. Access will rearrange this when you save and close the query.

Field:[motcode]
Criteria: [Forms]![Case-Based Query]![motcode] OR
[Forms]![Case-BasedQuery]![motcode] is Null

If you aren't using the query grid to build the query then

WHERE (MotCode = [Forms]![Case-Based Query]![motcode] OR
[Forms]![Case-BasedQuery]![motcode] is Null) ...


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200508/1

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.
Nulls are never equal to anything including other nulls. That's why you can use
Null = Null for a comparision, but can use the IS NULL operator.

Changing motcode to a text should have converted the values to their string
equivalents, except for the nulls which would remain null.

Did you try the code I suggested? Did it fail?

Another option, which may make your query slower is to use the nz function to
assign a never valid value to the motcode when it is null.

Something like:

Field: NZ([combined simple].motcode,-999)
Criteria: IIf([Forms]![Case-Based Query]![motcode] Is Not Null,
[Forms]![Case-Based Query]![motcode],
NZ([combined simple].[motcode],-999))

"Steven L via AccessMonster.com" wrote:

Hi, thanks for the reply and the help.

Fortunately or unfortunately, I've found out that the null value has little
to do with what's going wrong with the query.

I took apart the query to see what was going wrong and found out that just
using the below as a criteria gave perfect results:
======================
([combined simple].ccr)=IIf([Forms]![Case-Based Query]![ccr] Is Not Null,
[Forms]![Case-Based Query]![ccr], [combined simple].[ccr])
======================

But then when I added this criteria below all records that had a null value
for motcode didn't show up.
======================
([combined simple].motcode)=IIf([Forms]![Case-Based Query]![motcode] Is
Not Null,[Forms]![Case-Based Query]![motcode],[combined simple].[motcode])
======================

I can't see why this would be, as both criteria are exactly the same, and the
ccr field contains several null values as well.

I thought it may have something to do with the two fields containing
different data types (ccr contains text, motcode contains doubles), so I
changed motcode into text and all it's properties so that they were the same
as ccr, but yielded the same results.

Very confused. Hope you can help.

Steve

John Spencer (MVP) wrote:
If you are using the query grid then enter the following all in one criteria
cell. Access will rearrange this when you save and close the query.

Field:[motcode]
Criteria: [Forms]![Case-Based Query]![motcode] OR
[Forms]![Case-BasedQuery]![motcode] is Null

If you aren't using the query grid to build the query then

WHERE (MotCode = [Forms]![Case-Based Query]![motcode] OR
[Forms]![Case-BasedQuery]![motcode] is Null) ...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200508/1

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".

"John Spencer (MVP)" wrote:

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.
Nulls are never equal to anything including other nulls. That's why you can use
Null = Null for a comparision, but can use the IS NULL operator.

Changing motcode to a text should have converted the values to their string
equivalents, except for the nulls which would remain null.

Did you try the code I suggested? Did it fail?

Another option, which may make your query slower is to use the nz function to
assign a never valid value to the motcode when it is null.

Something like:

Field: NZ([combined simple].motcode,-999)
Criteria: IIf([Forms]![Case-Based Query]![motcode] Is Not Null,
[Forms]![Case-Based Query]![motcode],
NZ([combined simple].[motcode],-999))

"Steven L via AccessMonster.com" wrote:

Hi, thanks for the reply and the help.

Fortunately or unfortunately, I've found out that the null value has little
to do with what's going wrong with the query.

I took apart the query to see what was going wrong and found out that just
using the below as a criteria gave perfect results:
======================
([combined simple].ccr)=IIf([Forms]![Case-Based Query]![ccr] Is Not Null,
[Forms]![Case-Based Query]![ccr], [combined simple].[ccr])
======================

But then when I added this criteria below all records that had a null value
for motcode didn't show up.
======================
([combined simple].motcode)=IIf([Forms]![Case-Based Query]![motcode] Is
Not Null,[Forms]![Case-Based Query]![motcode],[combined simple].[motcode])
======================

I can't see why this would be, as both criteria are exactly the same, and the
ccr field contains several null values as well.

I thought it may have something to do with the two fields containing
different data types (ccr contains text, motcode contains doubles), so I
changed motcode into text and all it's properties so that they were the same
as ccr, but yielded the same results.

Very confused. Hope you can help.

Steve

John Spencer (MVP) wrote:
If you are using the query grid then enter the following all in one criteria
cell. Access will rearrange this when you save and close the query.

Field:[motcode]
Criteria: [Forms]![Case-Based Query]![motcode] OR
[Forms]![Case-BasedQuery]![motcode] is Null

If you aren't using the query grid to build the query then

WHERE (MotCode = [Forms]![Case-Based Query]![motcode] OR
[Forms]![Case-BasedQuery]![motcode] is Null) ...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200508/1

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/For...eries/200508/1

SCRATH THAT! Works now!

I didn't spot the typo in your first solution, hence the pop-up boxes. Thanks
you so much. You've saved my life.

Steve

Steven L wrote:
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

Whoops! Change the last line of the first paragraph to read

[quoted text clipped - 6 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/For...eries/200508/1