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

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

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

"Coll" wrote in message
news:fcc7a545-4c5a-4775-b4ae-4dbe96dbd9c9@f36g2000hsa.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_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/AlbertKallal/ridesrpt/ridesrpt.ht ml


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

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

On Apr 19, 9:58=A0pm, "Albert D. Kallal"
wrote:
> "Coll" wrote in message
>
> news:fcc7a545-4c5a-4775-b4ae-4dbe96dbd9c9@f36g2000hsa.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_category" in my query will have the
> > following criteria - =A0not "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 conditio=
n,
> 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" t=
he
> report.
>
> The code behind our button to launch the form would be:
>
> dim =A0 strWhere =A0 =A0 =A0 =A0as string
>
> if me.MyCheckBox =3D true then
> =A0 =A0strWhere =3D "employment_category <> 'not temp'" & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 " 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/AlbertKallal/ridesrpt/ridesrpt.ht ml
>
> --
> Albert D. Kallal =A0 =A0(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...?

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

"Coll" wrote in message
news:a79a90a1-ef90-4e08-948a-


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

you can use wild cards, or spell out the 4 actual names.

just go:

strWhere = "employment_category <> 'not temp'" & _
" and employment_category <> 'diem'" & _
" and employment_catagoery <> '*temp*'"


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

Hum, why are you opening the query????

Hum...(ouch!!). Yes, it is quite a bit of difference. Forms and reports
easily take a "where" clause. However, a query does NOT take well to this
at all.

To keep coding to a min, build the "base" query in the query builder
(and, we can use that query sol in our code). So, build the query for export
with all of the fields that you plan to export.

Don't give it any order, and don't give it any conditions...we add those
via
code...

our approach is thus:

build the original query (we will "copy" this in our code).
Lets call this qryBase

Now, also save the above query as "qryExcel" (use whatever name you want,
but my code example uses qryExcel)

So, we now have two identical queries.....

In our code we will now go:

grab the sql text from this "original" query
build our "where" clause as above
save this query + where + orderby into our 2nd query
This 2nd query "qeryExcel" can thus be exported for excel etc...

Dim strWhere As String
Dim strSql As String

If Me.MyCheckBox = True Then
strWhere = "employment_category <> 'not temp'" & _
" and employment_category <> 'diem'" & _
" and employment_catagory <> '*temp*'"
End If

strSql = CurrentDb.QueryDefs("qryBase").SQL
strSql = Left(strSql, Len(strSql) - 2) ' remove ";"

strSql = strSql & " where " & strWhere
strSql = strSql & " order by fieldnames"

CurrentDb.QueryDefs("qryExcel").SQL = strSql

At this point, we now have a query ready for use. Note that we use qryExcel
over and over for our output. It will have our conditions and ordery by in
it. As
mentioned, we don't have to use "two" quires, and could write the sql direct
in code..but, as mentioned...I am lazy and find the above more easy. Note
that
if your database is multi-user, then the above code will not with more then
one user at the same time unless you split your database (but, for any multi
user applications...you likely already split the app anyway...).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com