Hi Guys
The below code to filter a listbox which works fine if we use supervisor filter and ActivityType filter but it doesn't work for MonthRef filter. MonthRef field has datatype 'Date/Time'
Can anyone please help me in this?
Thanks
The below code to filter a listbox which works fine if we use supervisor filter and ActivityType filter but it doesn't work for MonthRef filter. MonthRef field has datatype 'Date/Time'
Code:
Private Sub cmdSearch()
Dim db As Database
Dim rs As Recordset
Dim bCriteriaEntered As Boolean
Dim sSQL As String
bCriteriaEntered = False
Me.lblStandards.Visible = True
Me.lstAllocation.Visible = True
sSQL = "SELECT * FROM qry_RMS_MinStand_AllocationList WHERE "
If Me.cboSupervisor <> "" Then
sSQL = sSQL & "[SupRef] = '" & Me.cboSupervisor & "' AND "
bCriteriaEntered = True
End If
If Me.cboMonthDue <> "" Then
sSQL = sSQL & "[MonthRef]=#" & [Forms]![frm_RMS_MinStand_UpdateAllocation]![cboMonthDue] & "# And "
bCriteriaEntered = True
MsgBox sSQL
End If
If Me.cboActivityType <> "" Then
sSQL = sSQL & "[ActivityTypeID] = " & Me.cboActivityType & " AND "
bCriteriaEntered = True
End If
If Right(sSQL, 5) = " AND " Then
sSQL = Left(sSQL, Len(sSQL) - 5)
End If
If Right(sSQL, 7) = " WHERE " Then
sSQL = Left(sSQL, Len(sSQL) - 7)
End If
Me.lstAllocation.RowSource = ""
Me.lstAllocation.RowSource = sSQL
If bCriteriaEntered = False Then
MsgBox "No criteria was entered", vbExclamation, "RMS"
End If
Thanks