"You canceled the previous operation."

[Steven L via AccessMonster.com]

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 are:
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)));

 

[Steven L via AccessMonster.com]

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?

 

[Steven L via AccessMonster.com]

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

 

[Steven L via AccessMonster.com]

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

 

[John Spencer (MVP)]

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]

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

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

 

[John Spencer (MVP)]

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))

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

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

 

[John Spencer (MVP)]

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

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

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]

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

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

 

[Steven L via AccessMonster.com]

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

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

 

[John Spencer (MVP)]

HOOORAY!!!

Sorry about the typo. I do that. Part of the reason is that I avoid object
names with spaces and special characters. That way I can get away with not
using the [] around items.

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

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

 

[Steven L via AccessMonster.com]

Hmm, not out of the woods yet it seems. Using the code fragement you wrote
works beautifully on all fields with comboboxes, but I'm now having trouble
with the five checkboxes remaining. Unlike the comboboxes where I could just
accept the values currently in the combobox, for the checkboxes I need to set
the value equal to '1' if it's checked. So I've been using IIf functions,
which is currently giving me "too complicated to evaluate" grief.

Just for reference, the query now looks like this:
==================================================================
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].c_year)=
[Forms]![Case-Based Query]![c_year] Or [Forms]![Case-Based Query]![c_year] Is
Null)
AND
(([combined simple].c_18district)=
[Forms]![Case-Based Query]![c_18district] Or [Forms]![Case-Based Query]!
[c_18district] Is Null)
AND
(([combined simple].org_case_no)=
[Forms]![Case-Based Query]![org_case_no] Or [Forms]![Case-Based Query]!
[org_case_no] Is Null)
AND
(([combined simple].ccr)=[Forms]![Case-Based Query]![ccr] Or [Forms]![Case-
Based Query]![ccr] Is Null) AND
(([combined simple].motcode)=
[Forms]![Case-Based Query]![motcode] Or [Forms]![Case-Based Query]![motcode]
Is Null)
AND
(([combined simple].vsex)=
[Forms]![Case-Based Query]![vsex] Or [Forms]![Case-Based Query]![vsex] Is
Null)
AND
(([combined simple].vage)=
[Forms]![Case-Based Query]![vage] Or [Forms]![Case-Based Query]![vage] Is
Null)
AND
(([combined simple].offsexcode)=
[Forms]![Case-Based Query]![offsexcode] Or [Forms]![Case-Based Query]!
[offsexcode] Is Null)
AND
(([combined simple].offage)=
[Forms]![Case-Based Query]![offage] Or [Forms]![Case-Based Query]![offage] Is
Null)
AND
(([combined simple].relacode)=
[Forms]![Case-Based Query]![relacode] Or [Forms]![Case-Based Query]![relacode]
Is Null)
AND
(([combined simple].weafirea)=
[Forms]![Case-Based Query]![weafirea] Or [Forms]![Case-Based Query]![weafirea]
Is Null)
);
===============================================================


Now I'm trying to add another criteria in there for the checkboxes. I figure
I have to use the IIf function to tell the query to set [combined simple].
viet_camp to '1' (values for viet_camp are stored as text) if [Forms]![Case-
Based Query]![viet_camp] is -1. So far it works fine when the box is checked,
i.e. assigning the value '1' works. However, when I don't check the box I
can't seem to bring up the records with empty strings for viet_camp. So far
I've tried:

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[combined simple].viet_camp]
)
----> Gives results where viet_camp equals 0 or 1, but not zero string values
(viet_camp can have values of "0", "1" and "").

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[Forms]![Case-Based Query]!
[viet_camp])
----> Gives results where viet_camp=0. [Forms]![Case-Based Query]![viet_camp]
will, of course, only give values -1 and 0, so I can see why only the 0
results are coming up.

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[Forms]![Case-Based Query]!
[viet_camp] Or [Forms]![Case-Based Query]![viet_camp] Is Null)
----> "Too complex to be evaluated"

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[Forms]![Case-Based Query]!
[viet_camp] Is Null)
----> Again, "too complex to be evaluated"

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',Null)
----> Returned no records whatsoever.

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[combined simple].viet_camp
Or [combined simple].viet_camp] Is Null)
----> "too complicated to be evaluated"

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[combined simple].viet_camp
Or "")
----> "too complicated to be evaluated"

Any way I can force the query to include the records where viet_camp is an
empty string?

Thanks again for all the help.

Steve


P.S. The typo's really my fault for using names with spaces in them. This is
my first Access project and I find myself putting spaces in names simply
because I can. Ugly habit, I know.

P.P.S. This query is killing my comp's memory. I now can only edit it through
sql view as apparently there's not enough memory to view it in design view.
Doesn't particularly bother me though, I prefer the sql view, but I hope it
doesn't bring the whole database crashing down some day...

HOOORAY!!!

Sorry about the typo. I do that. Part of the reason is that I avoid object
names with spaces and special characters. That way I can get away with not
using the [] around items.

SCRATH THAT! Works now!

[quoted text clipped - 45 lines]

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

 

[John Spencer (MVP)]

The problem is you have too many criteria to use the technique. Everytime you
add another criterion, Access will redo the query criteria (internally) and add
many more lines. Eventually it gets too complex for Access/Jet to handle.

If you need to evaluate 5 checkboxes and 11 comboboxes, the code is going to be
too complex. It looks as if you may have to build the query using VBA.

As far as turning the checkbox into 1 from -1, I would simply use the abs function.

Abs([Forms]![Case-Based Query]![viet_camp])
will return 1 or 0 assuming that viet_camp is a two-state checkbox.

As I said, you will probably have to go to building the query statement using
VBA. How are you using the query results? As a source for a report or a form?
Or in some other manner? And how familar are you with using VBA?

Hmm, not out of the woods yet it seems. Using the code fragement you wrote
works beautifully on all fields with comboboxes, but I'm now having trouble
with the five checkboxes remaining. Unlike the comboboxes where I could just
accept the values currently in the combobox, for the checkboxes I need to set
the value equal to '1' if it's checked. So I've been using IIf functions,
which is currently giving me "too complicated to evaluate" grief.

Just for reference, the query now looks like this:
==================================================================
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].c_year)=
[Forms]![Case-Based Query]![c_year] Or [Forms]![Case-Based Query]![c_year] Is
Null)
AND
(([combined simple].c_18district)=
[Forms]![Case-Based Query]![c_18district] Or [Forms]![Case-Based Query]!
[c_18district] Is Null)
AND
(([combined simple].org_case_no)=
[Forms]![Case-Based Query]![org_case_no] Or [Forms]![Case-Based Query]!
[org_case_no] Is Null)
AND
(([combined simple].ccr)=[Forms]![Case-Based Query]![ccr] Or [Forms]![Case-
Based Query]![ccr] Is Null) AND
(([combined simple].motcode)=
[Forms]![Case-Based Query]![motcode] Or [Forms]![Case-Based Query]![motcode]
Is Null)
AND
(([combined simple].vsex)=
[Forms]![Case-Based Query]![vsex] Or [Forms]![Case-Based Query]![vsex] Is
Null)
AND
(([combined simple].vage)=
[Forms]![Case-Based Query]![vage] Or [Forms]![Case-Based Query]![vage] Is
Null)
AND
(([combined simple].offsexcode)=
[Forms]![Case-Based Query]![offsexcode] Or [Forms]![Case-Based Query]!
[offsexcode] Is Null)
AND
(([combined simple].offage)=
[Forms]![Case-Based Query]![offage] Or [Forms]![Case-Based Query]![offage] Is
Null)
AND
(([combined simple].relacode)=
[Forms]![Case-Based Query]![relacode] Or [Forms]![Case-Based Query]![relacode]
Is Null)
AND
(([combined simple].weafirea)=
[Forms]![Case-Based Query]![weafirea] Or [Forms]![Case-Based Query]![weafirea]
Is Null)
);
===============================================================

Now I'm trying to add another criteria in there for the checkboxes. I figure
I have to use the IIf function to tell the query to set [combined simple].
viet_camp to '1' (values for viet_camp are stored as text) if [Forms]![Case-
Based Query]![viet_camp] is -1. So far it works fine when the box is checked,
i.e. assigning the value '1' works. However, when I don't check the box I
can't seem to bring up the records with empty strings for viet_camp. So far
I've tried:

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[combined simple].viet_camp]
)
----> Gives results where viet_camp equals 0 or 1, but not zero string values
(viet_camp can have values of "0", "1" and "").

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[Forms]![Case-Based Query]!
[viet_camp])
----> Gives results where viet_camp=0. [Forms]![Case-Based Query]![viet_camp]
will, of course, only give values -1 and 0, so I can see why only the 0
results are coming up.

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[Forms]![Case-Based Query]!
[viet_camp] Or [Forms]![Case-Based Query]![viet_camp] Is Null)
----> "Too complex to be evaluated"

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[Forms]![Case-Based Query]!
[viet_camp] Is Null)
----> Again, "too complex to be evaluated"

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',Null)
----> Returned no records whatsoever.

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[combined simple].viet_camp
Or [combined simple].viet_camp] Is Null)
----> "too complicated to be evaluated"

[combined simple].viet_camp=
IIf([Forms]![Case-Based Query]![viet_camp]=-1,'1',[combined simple].viet_camp
Or "")
----> "too complicated to be evaluated"

Any way I can force the query to include the records where viet_camp is an
empty string?

Thanks again for all the help.

Steve

P.S. The typo's really my fault for using names with spaces in them. This is
my first Access project and I find myself putting spaces in names simply
because I can. Ugly habit, I know.

P.P.S. This query is killing my comp's memory. I now can only edit it through
sql view as apparently there's not enough memory to view it in design view.
Doesn't particularly bother me though, I prefer the sql view, but I hope it
doesn't bring the whole database crashing down some day...

HOOORAY!!!

Sorry about the typo. I do that. Part of the reason is that I avoid object
names with spaces and special characters. That way I can get away with not
using the [] around items.

SCRATH THAT! Works now!

[quoted text clipped - 45 lines]

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

 

[Steven L via AccessMonster.com]

Yeah I noticed the design view racking up lots of extra lines that I hadn't
put in.
I'm using the query as the source for a subform. I'm new to VBA, but think
I'm fairly familiar with it now. I'm fine with loops, subs, functions,
modules and doing stuff like opening records and navigating/adding/deleting
from them.

I've never run a query from vba though. I think I saw an example once though
where the user wrote a string to represent the SQL and then ran a command to
run it. Is that the way you were suggesting I do it? Seems a little fiddly. I
currently can't see the design view, but can see the SQL view and the results,
so I'm quite happy to continue on that way, unless there's a reason that
won't work. Is there?

As far as the checkbox is concerned, I want the database to find records
where vietcamp=1 when the checkbox is checked, but take any values (0, 1 and
null) if it's not checked. Can't seem to force the query to accept the null
value though.

Steve

The problem is you have too many criteria to use the technique. Everytime you
add another criterion, Access will redo the query criteria (internally) and add
many more lines. Eventually it gets too complex for Access/Jet to handle.

If you need to evaluate 5 checkboxes and 11 comboboxes, the code is going to be
too complex. It looks as if you may have to build the query using VBA.

As far as turning the checkbox into 1 from -1, I would simply use the abs function.

Abs([Forms]![Case-Based Query]![viet_camp])
will return 1 or 0 assuming that viet_camp is a two-state checkbox.

As I said, you will probably have to go to building the query statement using
VBA. How are you using the query results? As a source for a report or a form?
Or in some other manner? And how familar are you with using VBA?

Hmm, not out of the woods yet it seems. Using the code fragement you wrote
works beautifully on all fields with comboboxes, but I'm now having trouble

[quoted text clipped - 130 lines]

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

 

[John Spencer (MVP)]

What kind of field is VietCamp? Number field?

You can build the SQL string in VBA and then assign the string (if it is not TOO
long) as the record source of the form (subForm). An alternative is to have a
named query as the record source and edit its SQL.

Rough (Very Rough) Code

Dim StrSQL as String
Dim strWhere as String

strSQL = "SELECT org_case_no, ccr, c_year, c_18district, motcode," & _
"vsex, vage, offsexcode, offage, relacode, viet_camp," & _
"c_rob_bur_c, vic_nonc, offchi, sex_as_c, weafirea, ID " & _
" FROM [combined simple] AS CS"

If IsNull([Forms]![Case-Based Query]![c_year]) = False then
StrWHERE = StrWhere & " AND c_Year= " & [Forms]![Case-Based Query]![c_year]
End If

'Assumes c_18District is a string
If IsNull([Forms]![Case-Based Query]![c_18district]) = False then
StrWhere= StrWhere &" AND c_18district)=" & _
Chr(34) & [Forms]![Case-Based Query]![c_18district] & Chr(34)
End if
...

IF Forms]![Case-Based Query]![viet_camp] = -1 Then
StrWhere = StrWhere & " AND viet_camp = 1"
End If

....

StrWhere = Mid(strWhere, 6) ' Trim off the first " AND "
StrSQL = StrSQL & " WHERE " & StrWhere

'Now Assign that string to the record source of your form/subform object.

Yeah I noticed the design view racking up lots of extra lines that I hadn't
put in.
I'm using the query as the source for a subform. I'm new to VBA, but think
I'm fairly familiar with it now. I'm fine with loops, subs, functions,
modules and doing stuff like opening records and navigating/adding/deleting
from them.

I've never run a query from vba though. I think I saw an example once though
where the user wrote a string to represent the SQL and then ran a command to
run it. Is that the way you were suggesting I do it? Seems a little fiddly. I
currently can't see the design view, but can see the SQL view and the results,
so I'm quite happy to continue on that way, unless there's a reason that
won't work. Is there?

As far as the checkbox is concerned, I want the database to find records
where vietcamp=1 when the checkbox is checked, but take any values (0, 1 and
null) if it's not checked. Can't seem to force the query to accept the null
value though.

Steve

The problem is you have too many criteria to use the technique. Everytime you
add another criterion, Access will redo the query criteria (internally) and add
many more lines. Eventually it gets too complex for Access/Jet to handle.

If you need to evaluate 5 checkboxes and 11 comboboxes, the code is going to be
too complex. It looks as if you may have to build the query using VBA.

As far as turning the checkbox into 1 from -1, I would simply use the abs function.

Abs([Forms]![Case-Based Query]![viet_camp])
will return 1 or 0 assuming that viet_camp is a two-state checkbox.

As I said, you will probably have to go to building the query statement using
VBA. How are you using the query results? As a source for a report or a form?
Or in some other manner? And how familar are you with using VBA?

Hmm, not out of the woods yet it seems. Using the code fragement you wrote
works beautifully on all fields with comboboxes, but I'm now having trouble

[quoted text clipped - 130 lines]

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

 

[Steven L via AccessMonster.com]

Vietcamp is a number field.

"An alternative is to have a named query as the record source and edit its
SQL."
Yeah, this is what I'm doing at the moment, but I'm unable to get the query
to accept null values for vietcamp. Does running the sql query through vba
offer any advantages? I'm thinking that maybe I could run several queries in
vba (and use vba to check if vietcamp is checked) to get several sets of
reults and then join them up later on, perhaps like so:

query1:
select * from [combined simple]
where [combined simple].ccr=[Forms]![Case-Based Query]![ccr] Or [Forms]![Case-
Based Query]![ccr] Is Null
and [combined simple].org_case_no=[Forms]![Case-Based Query]![org_case_no] Or
[Forms]![Case-Based Query]![org_case_no] Is Null
and ...
....
....
.... and [combined simple].motcode=[Forms]![Case-Based Query]![motcode] Or
[Forms]![Case-Based Query]![motcode] Is Null

vba to check vietcamp: (call it query2)
If vietcamp=-1 Then
// write code to run this query: select * from [combined simple] where
vietcamp=1
Else
// write code to run query that will accept all values of vietcamp
End If

query3: (join them up)
select * from query1,query2
where query1.ID=query2.ID

I haven't tried it yet, but I think it will work. But I'm not sure how to go
about executing the third query. If I store the results of the first two
queries in recordsets, how will I refer to them in the third query? Is there
a way? I suppose I could create a bunch of real tables with nothing in them,
repopulate them each time the query is run and then do query three, but it
seems a little messy and would create more tables than I'd prefer. What do
you think would be the best way to go about this?

I also worry that it might be slow, what with all the extra queries and
joining. Also, if the entire query is executed through vba in the form, then
presumably other forms will have no way of accessing the query, which isn't a
problem right now, but could be later on if I design another form that uses
the same query. Unless, again, I store the results in a table after finishing
the query

Steve

What kind of field is VietCamp? Number field?

You can build the SQL string in VBA and then assign the string (if it is not TOO
long) as the record source of the form (subForm). An alternative is to have a
named query as the record source and edit its SQL.

Rough (Very Rough) Code

Dim StrSQL as String
Dim strWhere as String

strSQL = "SELECT org_case_no, ccr, c_year, c_18district, motcode," & _
"vsex, vage, offsexcode, offage, relacode, viet_camp," & _
"c_rob_bur_c, vic_nonc, offchi, sex_as_c, weafirea, ID " & _
" FROM [combined simple] AS CS"

If IsNull([Forms]![Case-Based Query]![c_year]) = False then
StrWHERE = StrWhere & " AND c_Year= " & [Forms]![Case-Based Query]![c_year]
End If

'Assumes c_18District is a string
If IsNull([Forms]![Case-Based Query]![c_18district]) = False then
StrWhere= StrWhere &" AND c_18district)=" & _
Chr(34) & [Forms]![Case-Based Query]![c_18district] & Chr(34)
End if
...

IF Forms]![Case-Based Query]![viet_camp] = -1 Then
StrWhere = StrWhere & " AND viet_camp = 1"
End If

...

StrWhere = Mid(strWhere, 6) ' Trim off the first " AND "
StrSQL = StrSQL & " WHERE " & StrWhere

'Now Assign that string to the record source of your form/subform object.

Yeah I noticed the design view racking up lots of extra lines that I hadn't
put in.

[quoted text clipped - 38 lines]

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

 

[Steven L via AccessMonster.com]

Actually ignore all that stuff I wrote for now as I haven't tried your code
yet. I'll report back here when if it succeeds or not. Thanks.

Steve

Vietcamp is a number field.

"An alternative is to have a named query as the record source and edit its
SQL."
Yeah, this is what I'm doing at the moment, but I'm unable to get the query
to accept null values for vietcamp. Does running the sql query through vba
offer any advantages? I'm thinking that maybe I could run several queries in
vba (and use vba to check if vietcamp is checked) to get several sets of
reults and then join them up later on, perhaps like so:

query1:
select * from [combined simple]
where [combined simple].ccr=[Forms]![Case-Based Query]![ccr] Or [Forms]![Case-
Based Query]![ccr] Is Null
and [combined simple].org_case_no=[Forms]![Case-Based Query]![org_case_no] Or
[Forms]![Case-Based Query]![org_case_no] Is Null
and ...
...
...
... and [combined simple].motcode=[Forms]![Case-Based Query]![motcode] Or
[Forms]![Case-Based Query]![motcode] Is Null

vba to check vietcamp: (call it query2)
If vietcamp=-1 Then
// write code to run this query: select * from [combined simple] where
vietcamp=1
Else
// write code to run query that will accept all values of vietcamp
End If

query3: (join them up)
select * from query1,query2
where query1.ID=query2.ID

I haven't tried it yet, but I think it will work. But I'm not sure how to go
about executing the third query. If I store the results of the first two
queries in recordsets, how will I refer to them in the third query? Is there
a way? I suppose I could create a bunch of real tables with nothing in them,
repopulate them each time the query is run and then do query three, but it
seems a little messy and would create more tables than I'd prefer. What do
you think would be the best way to go about this?

I also worry that it might be slow, what with all the extra queries and
joining. Also, if the entire query is executed through vba in the form, then
presumably other forms will have no way of accessing the query, which isn't a
problem right now, but could be later on if I design another form that uses
the same query. Unless, again, I store the results in a table after finishing
the query

Steve

What kind of field is VietCamp? Number field?

[quoted text clipped - 39 lines]

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

 

[Steven L via AccessMonster.com]

Worked like a charm! Thanks, you're a legend!

Steve

Actually ignore all that stuff I wrote for now as I haven't tried your code
yet. I'll report back here when if it succeeds or not. Thanks.

Steve

Vietcamp is a number field.

[quoted text clipped - 52 lines]

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