Report Pop-Up Filter question

[mortonsafari]

I have a report that filters the information within it by means of a ‘pop-up’ form filter.
It currently contains two combo boxes as [Filter1] (Postcode) and [Filter2] (Suburb).
It works great when just processing one field at a time eg. Postcode or Suburb, where only one value is filtered for. Both these fields are ‘text’ Data Types.

The ‘Set Filter’ button runs the following code:-
Private Sub b_Set_Click()
Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 2
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) - 4))
'Set the Filter property
Reports![Contacts].Filter = strSQL
Reports![Contacts].FilterOn = True
End If
End Sub

I want to add/include two more Filter combo boxes – [Filter3] (from Postcode) and [Filter4] (to Postcode) - to allow for searching between Postcode values eg. Postcode >= [Filter3] (say 4000) AND <=[Filter4] (say 4300).
In a query I would use in the Postcode field : >=[Filter3] And <=[Filter4] and it would then provide pop-ups asking for Parameter values for [Filter3] And [Filter4].

In the pop-up filter form for the report I have the After Update for [Filter3] set as:
Private Sub Filter3_AfterUpdate()
Me.Filter4.Requery
End Sub
which resets the values in combo box [Filter4] at values greater than the [Filter3] value selected.
The user can then select a value in combo box [Filter4] which is greater than the value selected in combo box [Filter3].

The user would select either [Filter1]:Postcode OR [Filter2]:Suburb OR [Filter3]:from Postcode AND [Filter4]:to Postcode.

I now am having problems getting the code right so that I can use the four [Filters] on the form and have the report filtered according to the para above.

What would I need to include in the Private Sub b_Set_Click() code to allow thing to work as I would like.

Any assistance appreciated.


mortonsafari

[June7]

Review http://allenbrowne.com/ser-62.html

[mortonsafari]

Thanks. I'll check things out and see what I can do.
Appreciate your help. Thanks again.

[mortonsafari]

That link was useful in showing how to do a number of things, but it didn't really address my issue where I have two references to the same field eg. Postcode, where I am trying to do a '>= [value] and <= [value]' on the same field of Postcode.
If anyone has ideas, shout them out.
Many thanks
mortonsafari

[June7]

Adapt the code. It has example for range criteria. Build the criteria as needed. Postcode is a text field?

[Postcode] BETWEEN 'start value' AND 'end value'

or

[Postcode] >= 'start value' AND [Postcode] <= 'end value'

[mortonsafari]

I have sorted out my problem with the 3rd and 4th combo boxes where I am using the >= AND <= Parameters using the same PostCode field and have changed the PostCode field to a Number and not text.

Now the 1st combo box which is was using in the code originally posted is now numerical and the code for that section of the action is giving the error of "Data type mismatch in criteria expression".
What changes would I need to make in the original code as shown at the start of this post to make it work.
Also I probably need to put in a message so that it users select a value in both combos it pops up to say "Select either Postcode OR Suburb".

Thanks everyone.

[June7]

Number field parameters do not use delimiters. Chr(34) is code for quote mark. The code is concatenating this as text delimiter. This would not be done for number field.

Date fields would use # delimiter.