forms_are_nightmares
Registered User.
- Local time
- Yesterday, 18:20
- Joined
- Apr 5, 2010
- Messages
- 71
Hello All,
I know exactly where the code is failing but for some reason, I can't figure out the code.
Scenario:
I have filters on a form that will allow a user to find specific records. All filters work with the exception of the combo boxes that specify a date range. All combo boxes are unbound. The code is below. If someone can help me, I'd appreciate it. The bold code is what is failing.
Private Sub Filter1_Click()
Dim sWhere As String
Dim strDateField As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Me.Filter = ""
Me.FilterOn = False
If Not IsNull(Me.ACmbo) Then
sWhere = sWhere & "[AName]='" & Me.ACmbo & "' And "
End If
If Not IsNull(Me.StoreCmbo) Then
sWhere = sWhere & "[Store]='" & Me.StoreCmbo & "' And "
End If
If Not IsNull(Me.StateCmbo) Then
sWhere = sWhere & "[State]='" & Me.StateCmbo & "' And "
End If
If Not IsNull(Me.SCmbo) Then
sWhere = sWhere & "[Pspecialist]='" & Me.SCmbo & "' And "
End If
If Not IsNull(Me.CMCmbo) Then
sWhere = sWhere & "[cmgr]='" & Me.CMCmbo & "' And "
End If
If Not IsNull(Me.CityCmbo) Then
sWhere = sWhere & "[City]='" & Me.CityCmbo & "' And "
End If
If IsDate(Me.txtStartDate) Then
sWhere = sWhere & "[Adate] = '" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & " And "
End If
If IsDate(Me.txtEndDate) Then
If sWhere <> vbNullString Then
sWhere = sWhere
End If
sWhere = sWhere & "[Adate] = (" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ") And "
End If
If Right(sWhere, 4) = "And " Then
sWhere = Left(sWhere, Len(sWhere) - 5)
End If
Me.Filter = sWhere
Me.FilterOn = True
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Matches Found Based on Criteria Entered!!"
Me.FilterOn = False
End If
If sWhere = "" Then
MsgBox "No Criteria Has Been Entered!!"
End If
End Sub
I know exactly where the code is failing but for some reason, I can't figure out the code.
Scenario:
I have filters on a form that will allow a user to find specific records. All filters work with the exception of the combo boxes that specify a date range. All combo boxes are unbound. The code is below. If someone can help me, I'd appreciate it. The bold code is what is failing.
Private Sub Filter1_Click()
Dim sWhere As String
Dim strDateField As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Me.Filter = ""
Me.FilterOn = False
If Not IsNull(Me.ACmbo) Then
sWhere = sWhere & "[AName]='" & Me.ACmbo & "' And "
End If
If Not IsNull(Me.StoreCmbo) Then
sWhere = sWhere & "[Store]='" & Me.StoreCmbo & "' And "
End If
If Not IsNull(Me.StateCmbo) Then
sWhere = sWhere & "[State]='" & Me.StateCmbo & "' And "
End If
If Not IsNull(Me.SCmbo) Then
sWhere = sWhere & "[Pspecialist]='" & Me.SCmbo & "' And "
End If
If Not IsNull(Me.CMCmbo) Then
sWhere = sWhere & "[cmgr]='" & Me.CMCmbo & "' And "
End If
If Not IsNull(Me.CityCmbo) Then
sWhere = sWhere & "[City]='" & Me.CityCmbo & "' And "
End If
If IsDate(Me.txtStartDate) Then
sWhere = sWhere & "[Adate] = '" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & " And "
End If
If IsDate(Me.txtEndDate) Then
If sWhere <> vbNullString Then
sWhere = sWhere
End If
sWhere = sWhere & "[Adate] = (" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ") And "
End If
If Right(sWhere, 4) = "And " Then
sWhere = Left(sWhere, Len(sWhere) - 5)
End If
Me.Filter = sWhere
Me.FilterOn = True
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Matches Found Based on Criteria Entered!!"
Me.FilterOn = False
End If
If sWhere = "" Then
MsgBox "No Criteria Has Been Entered!!"
End If
End Sub