Using a control on a form to determine criteria for a query

[Coll]

I have a form and a query. I would like to have some control on the
form (check box probably), that when selected will limit the criteria
for a field in a query. Here are the details....

When the checkbox (or whatever control is appropriate on the form) is
checked, the field "employment_cat egory" in my query will have the
following criteria - not "temp" and not "diem" - I've tried several
things, but I can't seem to figure out how to feed the criteria for
that field. Any suggestions?

Colleen

[Albert D. Kallal]

"Coll" <co*********@ho tmail.comwrote in message
news:fc******** *************** ***********@f36 g2000hsa.google groups.com...

>I have a form and a query. I would like to have some control on the
form (check box probably), that when selected will limit the criteria
for a field in a query. Here are the details....

When the checkbox (or whatever control is appropriate on the form) is
checked, the field "employment_cat egory" in my query will have the
following criteria - not "temp" and not "diem" - I've tried several
things, but I can't seem to figure out how to feed the criteria for
that field. Any suggestions?

Your request is a little bit more difficult. If it was "just" ONE condition,
it would be quite easy.
You would simply place the form name + control name right into the query
builder in the condition area.

In your case, we have to solve a few problems.

If the check box is NOT checked, then I assume you do NOT want the
conditions (the report will ignore..or show all).

Checking "one" checkbox to do "two" conditions will require you to write
some code.

The idea would be build form with a heck box. (and, a button to "launch" the
report.

The code behind our button to launch the form would be:

dim strWhere as string

if me.MyCheckBox = true then
strWhere = "employment_cat egory <'not temp'" & _
" and employment_cate gory <'diem'"
end if

docmd.OpenRepor t "nameOfReport", acViewPreview,, strWhere

Note that the above will NOT include records where employment_cate gory is
blank.

In the above you have to replace "myCheckBox " with the name of your check
box.

The above is likely the approach I would use, but it does mean you have to
write a bit of code...

Here is some more screen shots which use the above idea for reports:

http://www.members.shaw.ca/AlbertKal.../ridesrpt.html
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com

[Coll]

On Apr 19, 9:58*pm, "Albert D. Kallal" <PleaseNOOOsPAM [e-mail] >
wrote:

"Coll" <col_mcma...@ho tmail.comwrote in message

news:fc******** *************** ***********@f36 g2000hsa.google groups.com...

I have a form and a query. I would like to have some control on the
form (check box probably), that when selected will limit the criteria
for a field in a query. Here are the details....

When the checkbox (or whatever control is appropriate on the form) is
checked, the field "employment_cat egory" in my query will have the
following criteria - *not "temp" and not "diem" - I've tried several
things, but I can't seem to figure out how to feed the criteria for
that field. Any suggestions?

Your request is a little bit more difficult. If it was "just" ONE condition,
it would be quite easy.
You would simply place the form name + control name right into the query
builder in the condition area.

In your case, we have to solve a few problems.

If the check box is NOT checked, then I assume you do NOT want the
conditions (the report will ignore..or show all).

Checking "one" checkbox to do "two" conditions will require you to write
some code.

The idea would be build form with a heck box. (and, a button to "launch" the
report.

The code behind our button to launch the form would be:

dim * strWhere * * * *as string

if me.MyCheckBox = true then
* *strWhere = "employment_cat egory <'not temp'" & _
* * * * * * * " and employment_cate gory <'diem'"
end if

docmd.OpenRepor t "nameOfReport", acViewPreview,, strWhere

Note that the above will NOT include records where employment_cate gory is
blank.

In the above you have to replace "myCheckBox " with the name of your check
box.

The above is likely the approach I would use, but it does mean you have to
write a bit of code...

Here is some more screen shots which use the above idea for reports:

http://www.members.shaw.ca/AlbertKal.../ridesrpt.html

--
Albert D. Kallal * *(Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa [e-mail]

Thank you. One more question - just to complicate things a bit further
- if the box is checked, there are 4 possible field values that I
would want to exclude. Three of them include the word *temp* - can I
use your code with the wildcards or do I need to spell out each of
those 3 values. Also, I am opening a query rather than a report - it's
a data dump for users to then export to Excel - I'm assuming that
wouldn't cause any problems,and the code will still work with
docmd.openquery ...?