By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,956 Members | 1,722 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,956 IT Pros & Developers. It's quick & easy.

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

P: n/a
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_category" 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
Jun 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Coll" <co*********@hotmail.comwrote in message
news:fc**********************************@f36g2000 hsa.googlegroups.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_category" 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_category <'not temp'" & _
" and employment_category <'diem'"
end if

docmd.OpenReport "nameOfReport",acViewPreview,,strWhere

Note that the above will NOT include records where employment_category 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
Jun 27 '08 #2

P: n/a
On Apr 19, 9:58*pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
"Coll" <col_mcma...@hotmail.comwrote in message

news:fc**********************************@f36g2000 hsa.googlegroups.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_category" 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_category <'not temp'" & _
* * * * * * * " and employment_category <'diem'"
end if

docmd.OpenReport "nameOfReport",acViewPreview,,strWhere

Note that the above will NOT include records where employment_category 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
pleaseNOOSpamKal...@msn.com
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...?
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.