Help Coding with Search Form

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.
 
I

Ivan Grozney

Doc,

The OR needs to be set inside parentheses to make sure it gets evaluated
separate from all the ANDs.

Each LDepartment needs to be compared to the Me.cmbDepartment.

Try this. You might have to tweak the syntax but I think it should work for
you.

If Not IsNull(Me.cmbDepartment) Then
varWhere = (varWhere + " AND ") & "([LDepartment1] LIKE '" &
Me.cmbDepartment & "*'" OR [LDepartment2] LIKE '" & Me.cmbDepartment & "*'")
End If

HTH

Vanya
 
D

Doctor

Thanks for the help.

However I'm not saavy enough to find the syntax error. Tried a few things,
but to no avail.

When I wasn't able to do this, based on your logic, I tried two different IF
statements, one for LDepartment1 and one for LDepartment2. But this returned
no records when it should have.

Any more help with the syntax?

Ivan Grozney said:
Doc,

The OR needs to be set inside parentheses to make sure it gets evaluated
separate from all the ANDs.

Each LDepartment needs to be compared to the Me.cmbDepartment.

Try this. You might have to tweak the syntax but I think it should work for
you.

If Not IsNull(Me.cmbDepartment) Then
varWhere = (varWhere + " AND ") & "([LDepartment1] LIKE '" &
Me.cmbDepartment & "*'" OR [LDepartment2] LIKE '" & Me.cmbDepartment & "*'")
End If

HTH

Vanya


Doctor said:
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.
Click to expand...
Click to expand...
 
I

Ivan Grozney

Doc,

I'll have to come up with a test real quick. I promise to get back
to you soon as I can.

Vanya

Doctor said:
Thanks for the help.

However I'm not saavy enough to find the syntax error. Tried a few things,
but to no avail.

When I wasn't able to do this, based on your logic, I tried two different IF
statements, one for LDepartment1 and one for LDepartment2. But this returned
no records when it should have.

Any more help with the syntax?

Ivan Grozney said:
Doc,

The OR needs to be set inside parentheses to make sure it gets evaluated
separate from all the ANDs.

Each LDepartment needs to be compared to the Me.cmbDepartment.

Try this. You might have to tweak the syntax but I think it should work for
you.

If Not IsNull(Me.cmbDepartment) Then
varWhere = (varWhere + " AND ") & "([LDepartment1] LIKE '" &
Me.cmbDepartment & "*'" OR [LDepartment2] LIKE '" & Me.cmbDepartment & "*'")
End If

HTH

Vanya


Doctor said:
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.
Click to expand...
Click to expand...
Click to expand...
 
I

Ivan Grozney

Doc,

Sorry about that. Try this



varWhere = (varWhere + " AND ") & "([LDepartment1] LIKE '" &
Me.cmbDepartment & "*' OR " & _
"[LDepartment2] LIKE '" &
Me.cmbDepartment & "*' )"


I had the ( and ) in the wrong spot.

HTH Vanya

Doctor said:
Thanks for the help.

However I'm not saavy enough to find the syntax error. Tried a few things,
but to no avail.

When I wasn't able to do this, based on your logic, I tried two different IF
statements, one for LDepartment1 and one for LDepartment2. But this returned
no records when it should have.

Any more help with the syntax?

Ivan Grozney said:
Doc,

The OR needs to be set inside parentheses to make sure it gets evaluated
separate from all the ANDs.

Each LDepartment needs to be compared to the Me.cmbDepartment.

Try this. You might have to tweak the syntax but I think it should work for
you.

If Not IsNull(Me.cmbDepartment) Then
varWhere = (varWhere + " AND ") & "([LDepartment1] LIKE '" &
Me.cmbDepartment & "*'" OR [LDepartment2] LIKE '" & Me.cmbDepartment & "*'")
End If

HTH

Vanya


Doctor said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Doctor

Thanks so much! That did the trick. Super.

Ivan Grozney said:
Doc,

Sorry about that. Try this



varWhere = (varWhere + " AND ") & "([LDepartment1] LIKE '" &
Me.cmbDepartment & "*' OR " & _
"[LDepartment2] LIKE '" &
Me.cmbDepartment & "*' )"


I had the ( and ) in the wrong spot.

HTH Vanya

Doctor said:
Thanks for the help.

However I'm not saavy enough to find the syntax error. Tried a few things,
but to no avail.

When I wasn't able to do this, based on your logic, I tried two different IF
statements, one for LDepartment1 and one for LDepartment2. But this returned
no records when it should have.

Any more help with the syntax?

Ivan Grozney said:
Doc,

The OR needs to be set inside parentheses to make sure it gets evaluated
separate from all the ANDs.

Each LDepartment needs to be compared to the Me.cmbDepartment.

Try this. You might have to tweak the syntax but I think it should work for
you.

If Not IsNull(Me.cmbDepartment) Then
varWhere = (varWhere + " AND ") & "([LDepartment1] LIKE '" &
Me.cmbDepartment & "*'" OR [LDepartment2] LIKE '" & Me.cmbDepartment & "*'")
End If

HTH

Vanya


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with WHERE filter on opening report from dialog 5
Add new record if nothing found with filter 2
Help With Search Form 1
Very strange subquery field disappeared with a filter 0
search coding 2
searching and finding records from separate tables 2
How to Open Main and Sub in VBA 1
Sorting Records 1

Top