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

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
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.

Code:
 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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
This looks like a misstep:
Code:
varWhere = (varWhere + " AND ") & "[FCIBLevelID] >= cboFromLevel"

The cboFromLevel should not be inside the quotes.
Perhaps:
Code:
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

Active Member
Joined
May 16, 2007
Messages
403
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top