D
Doctor
I have a search form that I use to search tblLeaders.
Our leaders can have more than one department assigned to them.
For instance, Tom, may have LDepartment1 assigned to 2 and have LDepartment2
assigned to 5.
In my search code, how do I get the one variable to search both fields
(LDeparement1 and LDepartment2) and return any record in which either field
matches the search criteria.
Here is the code that I have now. It works, but I can only get it to work
when searching one department but not both. At the bottom of this post, I
also copied my failed attempt which returned any record that had anything in
either LDepartment1 or LDepartment2.
Private Sub cmdSearch_Click()
Dim varWhere As Variant
' Initialize to Null
varWhere = Null
If Not IsNull(Me.txtFirstName) Then
varWhere = "[LFirstName] LIKE '" & Me.txtFirstName & "*'"
End If
If Not IsNull(Me.txtLastName) Then
varWhere = (varWhere + " AND ") & "[LLastName] LIKE '" &
Me.txtLastName & "*'"
End If
If Not IsNull(Me.cmbStatus) Then
varWhere = (varWhere + " AND ") & "[LStatus] LIKE '" & Me.cmbStatus
& "*'"
End If
If Not IsNull(Me.cmbDepartment) Then
varWhere = (varWhere + " AND ") & "[LDepartment1] LIKE '" &
Me.cmbDepartment & "*'"
End If
If Not IsNull(Me.cmbPosition) Then
varWhere = (varWhere + " AND ") & "[LPosition1] = " & Me.cmbPosition
End If
If IsNull(varWhere) Then
MsgBox "You must enter at least one search criteria.",
vbInformation, gstrAppTitle
Exit Sub
End If
If IsNull(DLookup("LeaderID", "tblLeaders", varWhere)) Then
MsgBox "No leaders meet your criteria.", vbInformation, gstrAppTitle
Exit Sub
End If
DoCmd.OpenForm "frmLeaders", WhereCondition:=varWhere
' DoCmd.Close acForm, Me.Name
End Sub
Here's a sample of what I tried but failed:
If Not IsNull(Me.cmbDepartment) Then
varWhere = (varWhere + " AND ") & "[LDepartment1] OR [LDepartment2]
LIKE '" & Me.cmbDepartment & "*'"
End If
Any help would be greatly appreciated.
Thanks.
Our leaders can have more than one department assigned to them.
For instance, Tom, may have LDepartment1 assigned to 2 and have LDepartment2
assigned to 5.
In my search code, how do I get the one variable to search both fields
(LDeparement1 and LDepartment2) and return any record in which either field
matches the search criteria.
Here is the code that I have now. It works, but I can only get it to work
when searching one department but not both. At the bottom of this post, I
also copied my failed attempt which returned any record that had anything in
either LDepartment1 or LDepartment2.
Private Sub cmdSearch_Click()
Dim varWhere As Variant
' Initialize to Null
varWhere = Null
If Not IsNull(Me.txtFirstName) Then
varWhere = "[LFirstName] LIKE '" & Me.txtFirstName & "*'"
End If
If Not IsNull(Me.txtLastName) Then
varWhere = (varWhere + " AND ") & "[LLastName] LIKE '" &
Me.txtLastName & "*'"
End If
If Not IsNull(Me.cmbStatus) Then
varWhere = (varWhere + " AND ") & "[LStatus] LIKE '" & Me.cmbStatus
& "*'"
End If
If Not IsNull(Me.cmbDepartment) Then
varWhere = (varWhere + " AND ") & "[LDepartment1] LIKE '" &
Me.cmbDepartment & "*'"
End If
If Not IsNull(Me.cmbPosition) Then
varWhere = (varWhere + " AND ") & "[LPosition1] = " & Me.cmbPosition
End If
If IsNull(varWhere) Then
MsgBox "You must enter at least one search criteria.",
vbInformation, gstrAppTitle
Exit Sub
End If
If IsNull(DLookup("LeaderID", "tblLeaders", varWhere)) Then
MsgBox "No leaders meet your criteria.", vbInformation, gstrAppTitle
Exit Sub
End If
DoCmd.OpenForm "frmLeaders", WhereCondition:=varWhere
' DoCmd.Close acForm, Me.Name
End Sub
Here's a sample of what I tried but failed:
If Not IsNull(Me.cmbDepartment) Then
varWhere = (varWhere + " AND ") & "[LDepartment1] OR [LDepartment2]
LIKE '" & Me.cmbDepartment & "*'"
End If
Any help would be greatly appreciated.
Thanks.