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