Prev: Update - Microsoft Responds to the Evolution of Community
Next: To index or not to index
From: dorney1 on 11 May 2010 10:51
I am attempting to make a filter by form search for a issues/comment database
that I created. I copied a form and VB template, switching out the names for
what are being used in my database, but keep receiving a runtime error. It
seems common from what I've read looking for answers, but everything that
I've found suggested I've tried and does not seem to be the problem. The
debug error is found at Me.Browse_All_Issues.Form.Filter = strWhere. Someone
please help!


Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Comment Information Query.[Assigned
To] = " & Me.AssignedTo & ""
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
'Add the predicate
strWhere = strWhere & " AND " & "Comment Information Query.[Opened By]
= " & Me.OpenedBy & ""
End If

' If Status
If Nz(Me.Status) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Comment Information Query.Status =
'" & Me.Status & "'"
End If

' If Category
If Nz(Me.Category) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Comment Information Query.CategoryID
= '" & Me.Category & "'"
End If

' If Priority
If Nz(Me.Priority) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Comment Information Query.Priority =
'" & Me.Priority & "'"
End If

' If Department
If Nz(Me.Department) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Comment Information Query.
DepartmentID = '" & Me.Department & "'"
End If

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Comment Information Query.[Opened
Date] >= " & GetDateFilter(Me.OpenedDateFrom)
ElseIf Nz(Me.OpenedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Opened Date To
If IsDate(Me.OpenedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Comment Information Query.[Opened
Date] <= " & GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Due Date From
If IsDate(Me.DueDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Comment Information Query.[Due Date]
>= " & GetDateFilter(Me.DueDateFrom)
ElseIf Nz(Me.DueDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Due Date To
If IsDate(Me.DueDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Comment Information Query.[Due Date]
<= " & GetDateFilter(Me.DueDateTo)
ElseIf Nz(Me.DueDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Title
If Nz(Me.ID) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Comment Information Query.ID Like
'*" & Me.ID & "*'"
End If

If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse All Issues", acFormDS, strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If

Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True
End If
End Sub

From: Duane Hookom on 11 May 2010 22:49
I seems that you have spaces in a query name but haven't wrapped the name in
[]s.
Have you tried a simple line of code prior to your error generating line
like:
Debug.Print "strWhere: " & strWhere

--
Duane Hookom
MS Access MVP

"dorney1" <u59984(a)uwe> wrote in message news:a7d93a31b2777(a)uwe...
> I am attempting to make a filter by form search for a issues/comment
> database
> that I created. I copied a form and VB template, switching out the names
> for
> what are being used in my database, but keep receiving a runtime error.
> It
> seems common from what I've read looking for answers, but everything that
> I've found suggested I've tried and does not seem to be the problem. The
> debug error is found at Me.Browse_All_Issues.Form.Filter = strWhere.
> Someone
> please help!
>
>
> Private Sub Search_Click()
> Const cInvalidDateError As String = "You have entered an invalid date."
> Dim strWhere As String
> Dim strError As String
>
> strWhere = "1=1"
>
> ' If Assigned To
> If Not IsNull(Me.AssignedTo) Then
> 'Create Predicate
> strWhere = strWhere & " AND " & "Comment Information
> Query.[Assigned
> To] = " & Me.AssignedTo & ""
> End If
>
> ' If Opened By
> If Not IsNull(Me.OpenedBy) Then
> 'Add the predicate
> strWhere = strWhere & " AND " & "Comment Information Query.[Opened
> By]
> = " & Me.OpenedBy & ""
> End If
>
> ' If Status
> If Nz(Me.Status) <> "" Then
> 'Add it to the predicate - exact match
> strWhere = strWhere & " AND " & "Comment Information Query.Status =
> '" & Me.Status & "'"
> End If
>
> ' If Category
> If Nz(Me.Category) <> "" Then
> 'Add it to the predicate - exact match
> strWhere = strWhere & " AND " & "Comment Information
> Query.CategoryID
> = '" & Me.Category & "'"
> End If
>
> ' If Priority
> If Nz(Me.Priority) <> "" Then
> 'Add it to the predicate - exact match
> strWhere = strWhere & " AND " & "Comment Information Query.Priority
> =
> '" & Me.Priority & "'"
> End If
>
> ' If Department
> If Nz(Me.Department) <> "" Then
> 'Add it to the predicate - exact match
> strWhere = strWhere & " AND " & "Comment Information Query.
> DepartmentID = '" & Me.Department & "'"
> End If
>
> ' If Opened Date From
> If IsDate(Me.OpenedDateFrom) Then
> ' Add it to the predicate - exact
> strWhere = strWhere & " AND " & "Comment Information Query.[Opened
> Date] >= " & GetDateFilter(Me.OpenedDateFrom)
> ElseIf Nz(Me.OpenedDateFrom) <> "" Then
> strError = cInvalidDateError
> End If
>
> ' If Opened Date To
> If IsDate(Me.OpenedDateTo) Then
> ' Add it to the predicate - exact
> strWhere = strWhere & " AND " & "Comment Information Query.[Opened
> Date] <= " & GetDateFilter(Me.OpenedDateTo)
> ElseIf Nz(Me.OpenedDateTo) <> "" Then
> strError = cInvalidDateError
> End If
>
> ' If Due Date From
> If IsDate(Me.DueDateFrom) Then
> ' Add it to the predicate - exact
> strWhere = strWhere & " AND " & "Comment Information Query.[Due
> Date]
>>= " & GetDateFilter(Me.DueDateFrom)
> ElseIf Nz(Me.DueDateFrom) <> "" Then
> strError = cInvalidDateError
> End If
>
> ' If Due Date To
> If IsDate(Me.DueDateTo) Then
> ' Add it to the predicate - exact
> strWhere = strWhere & " AND " & "Comment Information Query.[Due
> Date]
> <= " & GetDateFilter(Me.DueDateTo)
> ElseIf Nz(Me.DueDateTo) <> "" Then
> strError = cInvalidDateError
> End If
>
> ' If Title
> If Nz(Me.ID) <> "" Then
> ' Add it to the predicate - match on leading characters
> strWhere = strWhere & " AND " & "Comment Information Query.ID Like
> '*" & Me.ID & "*'"
> End If
>
> If strError <> "" Then
> MsgBox strError
> Else
> 'DoCmd.OpenForm "Browse All Issues", acFormDS, strWhere,
> acFormEdit,
> acWindowNormal
> If Not Me.FormFooter.Visible Then
> Me.FormFooter.Visible = True
> DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
> End If
>
> Me.Browse_All_Issues.Form.Filter = strWhere
> Me.Browse_All_Issues.Form.FilterOn = True
> End If
> End Sub
>
 | 
Pages: 1
Prev: Update - Microsoft Responds to the Evolution of Community
Next: To index or not to index