Run-time Error 3075 (1 Viewer)

[dmace1]

First, let me say I'm pretty inexperience in Access and very inexperienced in VBA but I'm trying to build an Access database and have built a search form. I have some VBA code to go along with that. Both the text boxes and the combo boxes work when I am searching in one field only. However, when I try to search on more than one field, I end up with a Run-Time error 3075 Syntax error (missing operator). In this case it says "Syntax error (missing operator) in query expression '([CaseType] Like "*JH Promotions/Marketing*") AND '([InitiatorType] Like "*JH Employee Faculty/Staff"*) AND'

This is what the entire code looks like:

Private Sub Command72_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.CaseTitle) Then
strWhere = strWhere & "([CaseTitle] Like ""*" & Me.CaseTitle & "*"") AND "
End If

If Not IsNull(Me.CaseSummary) Then
strWhere = strWhere & "([CaseSummary] Like ""*" & Me.CaseSummary & "*"") AND "
End If

If Not IsNull(Me.CaseType) Then
strWhere = strWhere & "([CaseType] Like ""*" & Me.CaseType & "*"") AND "
End If

If Not IsNull(Me.InitiatorType) Then
strWhere = strWhere & "([InitiatorType] Like ""*" & Me.InitiatorType & "*"") AND "
End If

If Not IsNull(Me.InitiatorName) Then
strWhere = strWhere & "([InitiatorName] Like ""*" & Me.InitiatorName & "*"") AND "
End If

If Not IsNull(Me.OutsideOrganizationType) Then
strWhere = strWhere & "([OutsideOrganizationType] Like ""*" & Me.OutsideOrganizationType & "*"") AND "
End If

If Not IsNull(Me.OutsideOrganizationName) Then
strWhere = strWhere & "([OutsideOrganizationName] Like ""*" & Me.OutsideOrganizationName & "*"") AND "
End If

If Not IsNull(Me.CommitteeContact) Then
strWhere = strWhere & "([CommitteeContact] Like ""*" & Me.CommitteeContact & "*"") AND "
End If

If Not IsNull(Me.Resolution) Then
strWhere = strWhere & "([Resolution] Like ""*" & Me.Resolution & "*"") AND "
End If
If Not IsNull(Me.AdditionalRemarksRegardingResolution) Then
strWhere = strWhere & "([AdditionalRemarksRegardingResolution] Like ""*" & Me.AdditionalRemarksRegardingResolution & "*"") AND "
End If

If Not IsNull(Me.BroughtToCommitteeMtg) Then
strWhere = strWhere & "([BroughttoCommitteeMtg] Like ""*" & Me.BroughtToCommitteeMtg & "*"") "
End If

DoCmd.OpenReport "UseOfNameCommitteeSpecialReport", acViewPreview, , strWhere
End Sub

Does anyone have any thoughts on why this isn't working for me?

Thank you for considering this.

 

[June7]

The trailing AND needs to be chopped off. This is why even the last item needs to include the AND then 'truncate'. Review code in http://allenbrowne.com/ser-62.html

Different approach is to include the AND based on IIf() conditional.

strWHERE = strWHERE & IIf(strWHERE <> "", " AND ", " ") & ...

 

[dmace1]

Thank you so much for your assistance! This worked!