Complicated Filtering Queries

A

Angela

Alright, things are getting complicated here! I figured out how to do a form
with a combo box where I select what I want to query to filter by clicking on
a command button. Now I want to know if there is a way to have check boxes in
this for various project types that I can select and unselect and when I run
the query so it will also filter for these. I can't really change it from
check boxes I don't think because I need to be able to choose a combination
of one or more project types. I'm sorry if my description of this problem
isn't very clear. I am new to these more advanced areas of Access and don't
know the proper words. Please let me know how I can explain this better.
Thanks for your time!

Angela
 
A

Angela

Ok, I figured out that part. Now I have another question. When select from
two different combo boxes, I want it to only be narrowed down to those two
selected. For example if I choose a name like Dan and then a project status
like Open, I get all of Dan's open projects and also everyone else's open
projects, when I really just wanted Dan's. Is there any way to do this?????
 
J

Joe D

Try this:

Add the check boxes to the form, we will assume they are named Check1 and
Check2 (the boxes, not the labels).

In the query, under the project type, in the criteria, add:

=iif(Forms![MyForm]![Check1]=Yes,"Type1","Nothing") or
iif(Forms![MyForm]![Check2]=Yes,"Type2","Nothing")

Of course, change the MyForm to your actual form name, the check box names
to actual names and the types to the real type names. I put the "Nothing" in
there for self documentation and so something would appear that was not a
project type.

There are undoubtedly other ways to do this, but I tested this and it works
as required.

HTH
 
J

James A. Fortune

Angela said:
Alright, things are getting complicated here! I figured out how to do a form
with a combo box where I select what I want to query to filter by clicking on
a command button. Now I want to know if there is a way to have check boxes in
this for various project types that I can select and unselect and when I run
the query so it will also filter for these. I can't really change it from
check boxes I don't think because I need to be able to choose a combination
of one or more project types. I'm sorry if my description of this problem
isn't very clear. I am new to these more advanced areas of Access and don't
know the proper words. Please let me know how I can explain this better.
Thanks for your time!

Angela
Click to expand...

Suppose your table has a Y/N field called PickMe (it's a small field
:)). A subform in datasheet view can show representative fields from
the table along with the PickMe field. You can click the ones you want
then use 'PickMe = -1' as your filter. It would also be good to have a
command button that resets the checkboxes back to 0.

James A. Fortune
 
A

Angela

I'm not sure what you mean here, but I figured out how to do the filtering
with the check boxes. Now I need to know how the do the next part! If it's
not one thing, it's another! I just never seem to come to the end with this
database stuff! :)
 
J

James A. Fortune

Angela said:
Ok, I figured out that part. Now I have another question. When select from
two different combo boxes, I want it to only be narrowed down to those two
selected. For example if I choose a name like Dan and then a project status
like Open, I get all of Dan's open projects and also everyone else's open
projects, when I really just wanted Dan's. Is there any way to do this?????

:
Click to expand...

For the filter use something like:

strFilter = "Employee = " & Chr(34) & cbxEmployee.Value & Chr(34) & "
And Status = " & Chr(34) & cbxStatus.Value & Chr(34)

For the example you gave it would look like:

Employee = "Dan" And Status = "Open"

when used in the report. If set in the Report_Open event then do
something like:

Report.Filter = "Employee = " & Chr(34) &
Forms!frmReportFilter!cbxEmployee.Value & Chr(34) & " And Status = " &
Chr(34) & Forms!frmReportFilter!cbxStatus.Value & Chr(34)

Report.FilterOn = True

James A. Fortune
 
Top