Filtering a report from a form (by a range of values)

[NekDAe13]

Hi,

I was hoping for some help. I am new to using VBA code but am learning.
I am trying to create a form that can filter a report based on selected criteria from a dropdown listbox. I have gotten this far with the following code:

Private Sub Command11_Click()


Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![MarkdownPriceReport1].Filter = strSQL
Reports![MarkdownPriceReport1].FilterOn = True
Else
Reports![MarkdownPriceReport1].FilterOn = False
End If

End Sub

And adding "Select Distinct [field1] from Query Order By [field1]" to the drop down list box's Row source. This works well, but I want to be able to select a range. For example, the list box currently shows the values in the field (1,2,3,4,5,6). I want to be able to select 2-6 or 1-3 or other combination. Can anyone give me advice on how to do this?

Thanks

 

[GOstlund]

I don't like the 'Filter' property much... haven't had much success with it, but what I've been using myself is just changing the .RecordSetSource to be my SQL statement instead of just pointing it to the table. This may or may not work in your situation, but I thought it worth the effort to mention.


----------
HTH
Gavin Ostlund