Using Forms to filter a query in Access 2007

[Jerry911]

Hi,

I have a query that I use to export data to a spreadsheet. The query itself works fine and I can manually edit the query to supply filtered information. What I would like to do is use a form that has several filter options so I do not have to modify my query every time I run it. I have a form with a list box to define one of the filters and I can make it work with that specific filter using the DoCmd.ApplyFilt er function. I want/need to add at least one more list box and possibly a couple of pick lists to create additional filter options.

The background for this is that the query gives me a subset of data with geographic coordinates that I import into a mapping application (MapPoint). I use this to analyze event locations based on changing criteria, such as Time of day and Day of week. Here is the code that I use in the form for the one filter that works:

[VB Code]...

Private Sub GeoReport_Click ()
Dim strFilter1 As String
Dim varItem As Variant
Dim intCount As Integer

For Each varItem In Me!List2.ItemsS elected
strFilter1 = strFilter1 & "[LEV3] = '" & _
Me![List2].ItemData(varIt em) & "' OR "
Next

' continue loop
'
' the next bit of code will subtract out the last "OR"
If strFilter1 <> "" Then
strFilter1 = Left(strFilter1 , Len(strFilter1) - 4)

For intCount = 1 To List2.ListCount
List2.Selected( intCount) = True
Next

Else
MsgBox "You did not select any zones!"
List2.SetFocus

Exit Sub

End If
'
' now, run the report using strFilter to pass a string
' containing the needed employees

DoCmd.OpenQuery "qry_GeoEventLo cation"
DoCmd.ApplyFilt er , strFilter1


End Sub

...[/End VB Code]

I have tried to incorporate additional string variables for additional list boxes (which is why you see the "strFilter1 ") without success. Any thoughts on how to attack this?

Thanks!

[nico5038]

I normally show the user how to use the right-click popup menu to filter a subform any way they want.
The print button then uses the build form.filter in the WHERE parameter to show the selected data.
This way I don't have to code all possible fields that can be filtered, as each visible field can be filtered by the user.

Idea ?

Nic;o)

[Jerry911]

I normally show the user how to use the right-click popup menu to filter a subform any way they want.
The print button then uses the build form.filter in the WHERE parameter to show the selected data.
This way I don't have to code all possible fields that can be filtered, as each visible field can be filtered by the user.

Idea ?

Nic;o)

Thanks for the fast response!

I think I know what you are describing, just not sure it's what I am looking for. It would probably be easier if I were just building a report where I have more flexibility in applying criteria, unfortunately I need the raw query results to be able to export them to a spreadsheet.

The 4 filter elements I need to use are a time range (pick list), a day range (pick List), a zone (list box) and event type (another list box). I want the flexibility of changing 1 or all of these variables to see the resultant data so I can plot it. Does that make sense?

[nico5038]

Yes, but you still are able to apply the result(s) of the selection fields to the form.filter of a datasheet subform and apply that to the report.
The main advantage is the fact that the user is able to see at the bottom the number of rows selected and is able to apply even more filters when needed.

For building your time and day selection you can use for each two comboboxes and test them being filled both before adding them to the form.filter of the subform.

For an area selection I often use an Area table with an additional PrintYesNo field.
Showing this in a subform will allow the user to check the wanted area's and for the report query you only have to JOIN this table with the condition that the PrintYesNo field is True.

Need more help ?

Nic;o)

[Jerry911]

Yes, but you still are able to apply the result(s) of the selection fields to the form.filter of a datasheet subform and apply that to the report.
The main advantage is the fact that the user is able to see at the bottom the number of rows selected and is able to apply even more filters when needed.

For building your time and day selection you can use for each two comboboxes and test them being filled both before adding them to the form.filter of the subform.

For an area selection I often use an Area table with an additional PrintYesNo field.
Showing this in a subform will allow the user to check the wanted area's and for the report query you only have to JOIN this table with the condition that the PrintYesNo field is True.

Need more help ?

Nic;o)

You have given me some food for thought. I will play with it some this weekend and see how it works for me. One thing I really like in this new version of Access is the ability to filter items easily in the datsheet view, that may ultimately be what I end up doing.

Thanks for your feedback!

[nico5038]

Nice to see that I gave some food for thought :-)
Shows me you're really eager to learn !

I've added the userguide I give my users to show them how to use the right-click popup.

Nic;o)