469,307 Members | 1,755 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

Help with query criteria

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.
Jun 27 '08 #1
1 1303
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(strSQL)

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

Regards

Kevin
"Coll" <co*********@hotmail.comwrote in message
news:e4**********************************@p25g2000 hsf.googlegroups.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.

Jun 27 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Steve Patrick | last post: by
10 posts views Thread by motessa | last post: by
4 posts views Thread by DeanL | last post: by
47 posts views Thread by Jo | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.