Filtering Data by Range

[abbyfaust]

I am having some trouble with filtering.

I have a form that has 6 subforms. The main form has 2 combo boxes that are stations along a road corridor. the filter is supposed to return the values from all 6 subforms that have stations between the 2 combo box stations.

It sort of works: I am getting all of the data up to the value that is in the second combo box instead of getting all of the data between the two combo boxes.

Here is the code that I have

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strFilter As String
Dim lngLen As Long

If Not IsNull(Me.cboSouthernIRP) Then
strFilter = strFilter & "[IRP 2006 Station] = """ & Me.cboSouthernIRP & """ OR "
End If

If Not IsNull(Me.cboSouthernIRP) Then
strFilter = strFilter & "[IRP 2006 Station] > """ & Me.cboSouthernIRP & """ AND "
End If

If Not IsNull(Me.cboNorthernIRP) Then
strFilter = strFilter & "[IRP 2006 Station] = """ & Me.cboNorthernIRP & """ OR "
End If

If Not IsNull(Me.cboNorthernIRP) Then
strFilter = strFilter & "[IRP 2006 Station] < """ & Me.cboNorthernIRP & """ AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No Criteria", vbInformation, "Nothing to do."
Else
strFilter = Left$(strFilter, lngLen)
Me.Filter = strFilter
Me.FilterOn = True
End If

End Sub

Private Sub cmdReset_Click()
Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "You cannot add new data to this form", vbInformation, "Permission Denied"
End Sub

I don't understand why the <= part would work but not the >= part...

I have used this forum a lot in the past few weeks and I was only able to get this far using the help that all of you have given to other people...Thanks a lot!

 

[pbaldy]

I suspect the immediate cause of your problem is confusion about the and's & or's. IOW, you end up with

this OR that AND this OR that

That will often cause unexpected results; you'd want:

(this OR that) AND (this OR that)

Note the parentheses clarify what you want. Having said that, you've got a couple of logical issues there. First, why test each control twice? After the first test, you know whether or not it's null or not, so you could do:

If Not IsNull(Me.cboSouthernIRP) Then
  strFilter = strFilter & "[IRP 2006 Station] = """ & Me.cboSouthernIRP & """ OR "
  strFilter = strFilter & "[IRP 2006 Station] > """ & Me.cboSouthernIRP & """ AND "
End If

Further, why not just do this

If Not IsNull(Me.cboSouthernIRP) Then
  strFilter = strFilter & "[IRP 2006 Station] >= """ & Me.cboSouthernIRP & """ AND "
End If

 

[abbyfaust]

Thanks! I was trying to make it too hard....

Works really well! My form returns all of the values that I want but on different records. I am trying to figure out what the problem is now...
Thanks again for your help.

Abby