My problem is with the query by form where i have a range of combo boxes and list boxes. The combo boxes are working fine, no problem however the multi select list boxes are the problem. I keep getting an error saying 'Data type Mismatch in criteria Expression'.
The structure of my database some would say is not the best way to go about it, as it is not fully normalised. However ive found that if i want to keep the same layout, displaying all the candidates data in list boxes and combo boxes then and using these features on the search form, then i am stuck with it.
The code i have used in the command button to start the filter process is displayed below. thanks for your time and efforts. Anyone got any ideas as to how i could get this resolved?
Expand|Select|Wrap|Line Numbers
- Private Sub cmdSearchCriteria_Click()
- Dim strWhere As String
- Dim lngLen As Long
- Dim varItem As Variant
- strWhere = "" ' Main filter
- 'Gender Search
- If Not IsNull(Me.cboGenderSearch) Then
- strWhere = strWhere & " ([tblCandidatesDetails].[Gender] = '" & Me.cboGenderSearch & "') AND "
- End If
- 'Nationality Search
- If Not IsNull(Me.cboNationalitySearch) Then
- strWhere = strWhere & " ([tblCandidatesDetails].[Nationality] = '" & Me.cboNationalitySearch & "') AND "
- End If
- 'Academic Level Search
- If Not IsNull(Me.cboAcademicLevelSearch) Then
- strWhere = strWhere & " ([tblCandidatesDetails].[AcademicLevel] = '" & Me.cboAcademicLevelSearch & "') AND "
- End If
- 'Mother Tongue Search
- If Not IsNull(Me.cboMotherTongue) Then
- strWhere = strWhere & " ([tblCandidatesDetails].[MotherTongue] = '" & Me.cboMotherTongue & "') AND "
- End If
- 'Military Area Search
- If Not IsNull(Me.cboMilitaryAreaInvolved) Then
- strWhere = strWhere & " ([tblCandidatesDetails].[WhatMilitaryareaInvolvedin] = '" & Me.cboMilitaryAreaInvolved & "') AND "
- End If
- 'Police Rank Search
- If Not IsNull(Me.cboPoliceRank) Then
- strWhere = strWhere & " ([tblCandidatesDetails].[PoliceRank] = '" & Me.cboPoliceRank & "') AND "
- End If
- 'Do they need military exp Check box Search
- If Me.chkDotheyhaveaMilitaryBackground = -1 Then
- strWhere = strWhere & " [tblCandidatesDetails!DotheyhaveaMilitaryBackground] = " & Me.chkDotheyhaveaMilitaryBackground
- End If
- '''''''''''''''Below is where the problems are'''''''''''''''''''''''''
- ' Check for SpokenLanguages in multiselect list
- If Not IsNull(Me.lstSpokenLang.Column(0)) Then
- For Each varItem In Me.lstSpokenLang.ItemsSelected
- strWhere = strWhere & " ([tblCandidatesDetails!SpokenLanguages] = """ & Me.lstSpokenLang.ItemData(varItem) & """) And "
- Next
- End If
- ' Check for Written Languages in multiselect list
- If Not IsNull(Me.lstWrittenlang.Column(0)) Then
- For Each varItem In Me.lstWrittenlang.ItemsSelected
- strWhere = strWhere & " ([tblCandidatesDetails!WrittenLanguages] = """ & Me.lstWrittenlang.ItemData(varItem) & """) And "
- Next
- End If
- ' Check for Professional Experience in multiselect list
- If Not IsNull(Me.lstProfessionalExpSearch.Column(0)) Then
- For Each varItem In Me.lstProfessionalExpSearch.ItemsSelected
- strWhere = strWhere & " ([tblCandidatesDetails!ProfessionalExperienceBackground] = """ & Me.lstProfessionalExpSearch.ItemData(varItem) & """) And "
- MsgBox Me.lstSpokenLang.ItemData(varItem)
- Next
- End If
- '***********************************************************************
- 'Chop off the trailing " AND ", and use the string as the form's Filter.
- '***********************************************************************
- 'See if the string has more than 5 characters (a trailng " AND ") to remove.
- lngLen = Len(strWhere) - 5
- If lngLen <= 0 Then 'Nah: there was nothing in the string.
- MsgBox "There has been No Crtieria selected", vbOKOnly, "Search Error"
- Else 'Yep: there is something there, so remove the " AND " at the end.
- strWhere = Left$(strWhere, lngLen)
- 'Finally, apply the string as the form's Filter.
- DoCmd.OpenForm "frmCriteriaSearchResults", acNormal, , WhereCondition:=strWhere
- End If
- End Sub