Help with query criteria

[Coll]

I have a database that produces different "cuts" of data for the user
to export into excel. The database essentially opens queries using
different criteria to filter the data as the user wants it. The user
can then print or export to excel depending on the need.

So I have a form that allows the user to specify how the data should
be cut. I'm stumped on one aspect of this. I have a field called
"Category" that has 10 possible values. None of the records have a
null value for this field. I would like to have a checkbox (or another
type of control) on my form that a user can check off. When the box is
checked, the query will open and exclude any records that have one of
5 values for that field. Any records with a value other than one of
those five would be displayed. Essentially, I am trying to exclude
temporary and per diem employees from the query (if the user wants
them excluded), and the category value for temps & per diems can be
any of 5 values. So it's not straightforward to exclude them.

I'm not a sophisticated programmer. So any simple approaches would be
appreciated. I had thought of setting up separate queries (so a
different query opens if the box is checked), but I need to repeat
this process on a few other queries, and this would be a cumbersome
approach.

Another thought (just occuring to me now) is to create new "field" in
the query (using code) that determines which employees are temps/per
diems with a case statement and returns a single uniform value for any
temps/per diems. And then, if the box is checked, send one value to
the criteria line of that new "field" to exclude them. Would that
work?

Thanks.

[KC-Mass]

Re: Help with query criteria

I don't know if you've done any queries in VBA code but I think that would
be the easy way.

Something like this air code:

strSQL = "Select * from tblPersonnel;"
'Thats your basic query - then you test for the checkbox
If Me.chkNoTemps then
strSQL = Left(strSQL ,len(strSQL)-1) ' this gets rid of the original
semi colon
' This adds the WHERE clause
strSQL = strSQL & " WHERE JobType NOT IN ('Temp', 'PerDiem', 'type3',
'type4','type5) ;"
End If

DoCmd.RunSQL(st rSQL)

' If the checkbox isn't checked you get everything. If it is you get
everthing but the temps et al.

Regards

Kevin
"Coll" <col_mcmanus@ho tmail.comwrote in message
news:e432251a-6511-48c6-95e6-9a6fe0f239b2@p2 5g2000hsf.googl egroups.com...

>I have a database that produces different "cuts" of data for the user
to export into excel. The database essentially opens queries using
different criteria to filter the data as the user wants it. The user
can then print or export to excel depending on the need.
>
So I have a form that allows the user to specify how the data should
be cut. I'm stumped on one aspect of this. I have a field called
"Category" that has 10 possible values. None of the records have a
null value for this field. I would like to have a checkbox (or another
type of control) on my form that a user can check off. When the box is
checked, the query will open and exclude any records that have one of
5 values for that field. Any records with a value other than one of
those five would be displayed. Essentially, I am trying to exclude
temporary and per diem employees from the query (if the user wants
them excluded), and the category value for temps & per diems can be
any of 5 values. So it's not straightforward to exclude them.
>
I'm not a sophisticated programmer. So any simple approaches would be
appreciated. I had thought of setting up separate queries (so a
different query opens if the box is checked), but I need to repeat
this process on a few other queries, and this would be a cumbersome
approach.
>
Another thought (just occuring to me now) is to create new "field" in
the query (using code) that determines which employees are temps/per
diems with a case statement and returns a single uniform value for any
temps/per diems. And then, if the box is checked, send one value to
the criteria line of that new "field" to exclude them. Would that
work?
>
Thanks.