Filter Filter Filter

[Guest]

i am creating a database for "Construction PROJECTS"...

it goes like this:
I have a form where a user can filter REPORT to generate using different
attributes like LOCATION, BUDGET, START DATE, END DATE and PROJECT STATUS...

PROJECT STATUS are:
- DESIGN
- PLANNED
- CONSTRUCTION
- AWARDED
- COMPLETED
- ON HOLD

now, what i want to do is to filter my report using multiple attributes..
like for example, I want to filter all the PROJECTS in USA where PROJECT
STATUS are equal to "DESIGN", "CONSTRUCTION" and "ON HOLD"...

I am using checkboxes for PROJECT STATUS so that I can choose one or more
than one status...

how am i goin to do such logic.. especially with the checkboxes...

thank you and hope you can help me with this..

Amsuria
---------------------------------------------

 

[kingston via AccessMonster.com]

One way to do this is to open the report with a where clause like:
"[Location]='USA' AND ([PROJECT STATUS]='DESIGN' OR [PROJECT STATUS]
='CONSTRUCTION' OR [PROJECT STATUS]='ON HOLD'"
You'll have to build the where clause based upon the user's selections by
doing something like this:

If CheckDesign = -1 then
StringFilter = String Filter & " OR [PROJECT STATUS]='DESIGN'"
...

You'll have to figure out where to put ANDs, ORs, and parentheses depending

 

[Guest]

I would suggest that Combo Boxes are a better choice of control for what you
are doing. It is not only easier for the user, it makes the coding much
easier. This type of filtering is usually done with Cascading Combo boxes.
The technique is to filter each combo based on the value selected in the
previous combo. For example, the row source for the second combo would look
something like:
SELECT Budget FROM MyTable WHERE Location = Me.cboLocation

Then in the After Update of each combo, requery the combo that follows it.
In this case, you would requery the Budget Combo in the After Update of the
Location combo.

Now, once all the selections are made and you wan't the report, use the
Click event of the report command button to construct a Where argument for
the report.

If Not IsNull(Me.cboLocation) Then
strWhere = "[Location] = '" & Me.cboLocation & "'"
End If

If Not IsNull(Me.cboBudget) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Budget] = " & Me.cboBudget
End If

'You can put as many of these together as you need

Docmd.OpenReport "MyReportName", , , strWhere

 

[Guest]

hi king,
tho i get your way/logic, i still need some codes or maybe - if you could
possibly elaborate a little more.. thanks

sorry, im a neophyte in Access...

thanks a lot..\

amsuria

One way to do this is to open the report with a where clause like:
"[Location]='USA' AND ([PROJECT STATUS]='DESIGN' OR [PROJECT STATUS]
='CONSTRUCTION' OR [PROJECT STATUS]='ON HOLD'"
You'll have to build the where clause based upon the user's selections by
doing something like this:

If CheckDesign = -1 then
StringFilter = String Filter & " OR [PROJECT STATUS]='DESIGN'"
...

You'll have to figure out where to put ANDs, ORs, and parentheses depending

i am creating a database for "Construction PROJECTS"...

it goes like this:
I have a form where a user can filter REPORT to generate using different
attributes like LOCATION, BUDGET, START DATE, END DATE and PROJECT STATUS...

PROJECT STATUS are:
- DESIGN
- PLANNED
- CONSTRUCTION
- AWARDED
- COMPLETED
- ON HOLD

now, what i want to do is to filter my report using multiple attributes..
like for example, I want to filter all the PROJECTS in USA where PROJECT
STATUS are equal to "DESIGN", "CONSTRUCTION" and "ON HOLD"...

I am using checkboxes for PROJECT STATUS so that I can choose one or more
than one status...

how am i goin to do such logic.. especially with the checkboxes...

thank you and hope you can help me with this..

Amsuria
---------------------------------------------

 

[kingston via AccessMonster.com]

How are you opening the report? If you are using a procedure, use something
like the If statement I provided to create the where clause. Then use the
where clause after your OpenReport command:

DoCmd.OpenReport "ReportName",acViewPreview,,StringWhere

Add a button to your form and use the wizard to create a procedure for
opening your report. This will give you a starting point for the code.
Constructing the where clause is simply a matter of checking the values in
your controls (it might have been clearer if I wrote this instead: If Me.
CheckBox = True Then..) and concatenating the pieces together.

Dim StringWhere as String

If Me.CheckBox1 = True Then
StringWhere = "[STATUS]='Design'"
End If

If Me.CheckBox2 = True
If IsNull(StringWhere) Then
StringWhere = "[STATUS]='ON HOLD'"
Else
StringWhere = StringWhere & " OR [STATUS]='ON HOLD'"
End If
End If

...

hi king,
tho i get your way/logic, i still need some codes or maybe - if you could
possibly elaborate a little more.. thanks

sorry, im a neophyte in Access...

thanks a lot..\

amsuria

One way to do this is to open the report with a where clause like:
"[Location]='USA' AND ([PROJECT STATUS]='DESIGN' OR [PROJECT STATUS]

[quoted text clipped - 36 lines]

 

[Guest]

thank you very much KINGSTON and KLATUU...

i never thought i would be able to filter such records...

again, thank you sooo much!!!

amsuria

I would suggest that Combo Boxes are a better choice of control for what you
are doing. It is not only easier for the user, it makes the coding much
easier. This type of filtering is usually done with Cascading Combo boxes.
The technique is to filter each combo based on the value selected in the
previous combo. For example, the row source for the second combo would look
something like:
SELECT Budget FROM MyTable WHERE Location = Me.cboLocation

Then in the After Update of each combo, requery the combo that follows it.
In this case, you would requery the Budget Combo in the After Update of the
Location combo.

Now, once all the selections are made and you wan't the report, use the
Click event of the report command button to construct a Where argument for
the report.

If Not IsNull(Me.cboLocation) Then
strWhere = "[Location] = '" & Me.cboLocation & "'"
End If

If Not IsNull(Me.cboBudget) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[Budget] = " & Me.cboBudget
End If

'You can put as many of these together as you need

Docmd.OpenReport "MyReportName", , , strWhere

i am creating a database for "Construction PROJECTS"...

it goes like this:
I have a form where a user can filter REPORT to generate using different
attributes like LOCATION, BUDGET, START DATE, END DATE and PROJECT STATUS...

PROJECT STATUS are:
- DESIGN
- PLANNED
- CONSTRUCTION
- AWARDED
- COMPLETED
- ON HOLD

now, what i want to do is to filter my report using multiple attributes..
like for example, I want to filter all the PROJECTS in USA where PROJECT
STATUS are equal to "DESIGN", "CONSTRUCTION" and "ON HOLD"...

I am using checkboxes for PROJECT STATUS so that I can choose one or more
than one status...

how am i goin to do such logic.. especially with the checkboxes...

thank you and hope you can help me with this..

Amsuria