Hi,
First, I'm fairly new to Access and even newer to VBA but trying. I created a search form and the coding. My search form has 11 searchable fields. All of them are working except one - CommitteeContact which is based on a combo box.
When I try to search on CommitteeContact, I get the error Runtime Error - 3831 - The multi-valued field '[CommitteeContact]" cannot be used in a WHERE or HAVING clause.
I'm guessing the error is because it is a combo box. I have 5 other combo boxes on the form and they are working correctly using the same coding.
Any suggestions here? My code is below. Thank you.
Option Compare Database
Option Explicit
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 & IIf(strWhere <> "", " AND ", "") & "([CaseTitle] Like ""*" & Me.CaseTitle & "*"") "
End If
If Not IsNull(Me.CaseSummary) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([CaseSummary] Like ""*" & Me.CaseSummary & "*"") "
End If
If Not IsNull(Me.CaseType) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([CaseType] Like ""*" & Me.CaseType & "*"") "
End If
If Not IsNull(Me.InitiatorType) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([InitiatorType] Like ""*" & Me.InitiatorType & "*"") "
End If
If Not IsNull(Me.InitiatorName) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([InitiatorName] Like ""*" & Me.InitiatorName & "*"") "
End If
If Not IsNull(Me.OutsideOrganizationType) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([OutsideOrganizationType] Like ""*" & Me.OutsideOrganizationType & "*"") "
End If
If Not IsNull(Me.OutsideOrganizationName) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([OutsideOrganizationName] Like ""*" & Me.OutsideOrganizationName & "*"") "
End If
If Not IsNull(Me.CommitteeContact) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([CommitteeContact] Like ""*" & Me.CommitteeContact & "*"") "
End If
If Not IsNull(Me.Resolution) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([Resolution] Like ""*" & Me.Resolution & "*"") "
End If
If Not IsNull(Me.AdditionalRemarksRegardingResolution) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([AdditionalRemarksRegardingResolution] Like ""*" & Me.AdditionalRemarksRegardingResolution & "*"") "
End If
If Not IsNull(Me.BroughtToCommitteeMtg) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([BroughttoCommitteeMtg] Like ""*" & Me.BroughtToCommitteeMtg & "*"") "
End If
DoCmd.OpenReport "UseOfNameCommitteeSpecialReport", acViewPreview, , strWhere
End Sub
First, I'm fairly new to Access and even newer to VBA but trying. I created a search form and the coding. My search form has 11 searchable fields. All of them are working except one - CommitteeContact which is based on a combo box.
When I try to search on CommitteeContact, I get the error Runtime Error - 3831 - The multi-valued field '[CommitteeContact]" cannot be used in a WHERE or HAVING clause.
I'm guessing the error is because it is a combo box. I have 5 other combo boxes on the form and they are working correctly using the same coding.
Any suggestions here? My code is below. Thank you.
Option Compare Database
Option Explicit
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 & IIf(strWhere <> "", " AND ", "") & "([CaseTitle] Like ""*" & Me.CaseTitle & "*"") "
End If
If Not IsNull(Me.CaseSummary) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([CaseSummary] Like ""*" & Me.CaseSummary & "*"") "
End If
If Not IsNull(Me.CaseType) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([CaseType] Like ""*" & Me.CaseType & "*"") "
End If
If Not IsNull(Me.InitiatorType) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([InitiatorType] Like ""*" & Me.InitiatorType & "*"") "
End If
If Not IsNull(Me.InitiatorName) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([InitiatorName] Like ""*" & Me.InitiatorName & "*"") "
End If
If Not IsNull(Me.OutsideOrganizationType) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([OutsideOrganizationType] Like ""*" & Me.OutsideOrganizationType & "*"") "
End If
If Not IsNull(Me.OutsideOrganizationName) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([OutsideOrganizationName] Like ""*" & Me.OutsideOrganizationName & "*"") "
End If
If Not IsNull(Me.CommitteeContact) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([CommitteeContact] Like ""*" & Me.CommitteeContact & "*"") "
End If
If Not IsNull(Me.Resolution) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([Resolution] Like ""*" & Me.Resolution & "*"") "
End If
If Not IsNull(Me.AdditionalRemarksRegardingResolution) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([AdditionalRemarksRegardingResolution] Like ""*" & Me.AdditionalRemarksRegardingResolution & "*"") "
End If
If Not IsNull(Me.BroughtToCommitteeMtg) Then
strWhere = strWhere & IIf(strWhere <> "", " AND ", "") & "([BroughttoCommitteeMtg] Like ""*" & Me.BroughtToCommitteeMtg & "*"") "
End If
DoCmd.OpenReport "UseOfNameCommitteeSpecialReport", acViewPreview, , strWhere
End Sub