Between "Text Field 1" And "Text Field 2"

[koolwaters]

I have a table which contains customer grouping information called tblCustomerLevel. The table structure is below
ID Level
1 SN1
2 SN2
3 SN3
4 SN4
5 SN5
6 SN6
7 SN7
8 SN8
9 SN9
10 SN10
11 SN11
12 CONTRA
13 FT
14 FTP

I have a filter form for reports, where I can preview a report based on one or multiple selections from several combo boxes. On this form, I have two combo boxes called "From" and "To". I need to have the ability to find all customers between two levels (between SN5 and SN10) so I would enter SN5 in the from combo box and SN10 in the two combo box. I also need to be able to filter for customers greater than or equal to a particular level (>=SN8).

The row source for the From and To combo boxes is tblCustomerLevel. I have added both fields, but they are bound to column 1. This is the code that I have in the Click event of the Preview command button.

 Private Sub cmdPreview_Click()
Dim varWhere As Variant
Dim strReport As String

    varWhere = Null
    
    If Not IsNothing(Me.cboStatus) Then
    varWhere = "[StatusID] LIKE '" & Me. cboStatus & "*'"
    End If
        
    If Not IsNothing(Me.cboAssignedEmployee) Then
    varWhere = (varWhere + " AND ") & "[EmployeeID] LIKE '" & Me. cboAssignedEmployee & "*'"
    End If
    
    If Not IsNothing(Me.cboCountry) Then
    varWhere = (varWhere + " AND ") & "[Country] LIKE '" & Me.cboCountry & "*'"
    End If
       
    If Not IsNothing(Me.cboFromLevel) Then
    MsgBox "You must enter a valid 'From' Level.", vbExclamation
    Me.cboFromLevel.SetFocus
    Exit Sub
    End If
    varWhere = (varWhere + " AND ") & "[FCIBLevelID] >= cboFromLevel"
     
    If Not IsNothing(Me.cboToLevel) Then
    MsgBox "You must enter a valid 'To' Level.", vbExclamation
    Me.cboToLevel.SetFocus
    Exit Sub
    End If

    If Not IsNothing(Me.cboFromLevel) Then
    If Me.cboToLevel < Me.cboFromLevel Then
    MsgBox "'To' Level must not be a higher level than 'From' Level.", vbExclamation
    Me.cboToLevel.SetFocus
    Exit Sub
    End If
    End If
    varWhere = (varWhere + " AND ") & "[FCIBLevelID] <= cboToLevel"
           
    If IsNull(varWhere) Then
    If vbNo = MsgBox("You did not enter any criteria." & _
    vbCrLf & vbCrLf & "Do you want to print the report " & _
    "for all records in the database?", _
    vbYesNo + vbQuestion + vbDefaultButton2) Then
    Exit Sub
    Else
    varWhere = "1 = 1"
    End If
    End If
    
    Me.Visible = False
    
    strReport = "Report1"
    
    DoCmd.OpenReport strReport, acViewPreview, _
    wherecondition:=varWhere
    
    DoCmd.SelectObject acReport, strReport
    
Print_Exit:
    DoCmd.Close acForm, Me.Name
    Exit Sub

End Sub

If I remove the code relating to the From and To, the code executes perfectly and I can filter the report by Status, Assigned Employee and Country or by all. When the code is in for the From and To, I get the parameter dialog box prompting for the From and To Levels.

My challange is that I am not sure how to filter the records for the From and To customer levels.

I appreciate any assistance. Thanks.

 

[xenou]

This looks like a misstep:

varWhere = (varWhere + " AND ") & "[FCIBLevelID] >= cboFromLevel"

The cboFromLevel should not be inside the quotes.
Perhaps:

varWhere = (varWhere + " AND ") & "[FCIBLevelID] >= '" & me.cboFromLevel & "' "

Of course altered as needed - you know about the need for single quotes with text and so on.

 

[koolwaters]

Still unable to get it to work that way.

What I think I will do is to use the Primary Key field as the field to enter greather than, less than, etc.

As usaul, thanks for any help.