Search Multiple Fields From Same String

[rjw24]

I have a range of unbound textboxes that operate as search boxes. I would
like to type a name (whether this be full, first or surname) in to the box
and then the database searches for a record where that name appears in ANY
one of those 3 fields. The same data does not exist in these fields but
related data does

I am unsure about whether I can do this using Allen Brownes serach form
tutorial. The code I have used is below and whilst this will search the one
field I have mentioned, if I use the Or function it does not work. Could
anyone help or at least point me in the right direction.

If Not IsNull(Me.txtSearch) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtSearch & ""*")"

End If

The other two fields I wish to search [Associates] and [Companies]

Thanks

 

[Allen Browne]

If Not IsNull(Me.txtSearch) Then
strWhere = strWhere & "(([Name] Like ""*" & Me.txtSearch & _
""*") OR ([Associates] Like ""*" & Me.txtSearch & _
""*") OR ([Companies] Like ""*" & Me.txtSearch & ""*"))"
End If

 

[rjw24]

Hi Allen,

Thanks for your help.

That didnt seem to work either, I got a "type mismatch error" - is that
possibly beacuse companies and associates are memo fields?

If Not IsNull(Me.txtSearch) Then
strWhere = strWhere & "(([Name] Like ""*" & Me.txtSearch & _
""*") OR ([Associates] Like ""*" & Me.txtSearch & _
""*") OR ([Companies] Like ""*" & Me.txtSearch & ""*"))"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a range of unbound textboxes that operate as search boxes. I would
like to type a name (whether this be full, first or surname) in to the box
and then the database searches for a record where that name appears in ANY
one of those 3 fields. The same data does not exist in these fields but
related data does

I am unsure about whether I can do this using Allen Brownes serach form
tutorial. The code I have used is below and whilst this will search the
one
field I have mentioned, if I use the Or function it does not work. Could
anyone help or at least point me in the right direction.

If Not IsNull(Me.txtSearch) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtSearch & ""*")"

End If

The other two fields I wish to search [Associates] and [Companies]

Thanks

 

[Allen Browne]

Sorry: I just copied'n'pasted yours. Quotes are wrong:

strWhere = strwhere & "(([Name] Like ""*" & Me.txtSearch & _
"*"") OR ([Associates] Like ""*" & Me.txtSearch & _
"*"") OR ([Companies] Like ""*" & Me.txtSearch & "*""))"

For debugging, add the line:
Debug.Print strWhere
just after the above. If it fails, open the Immediate Window (Ctrl+G) and
see what came out.

For details on how the quotes should work, see:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for your help.

That didnt seem to work either, I got a "type mismatch error" - is that
possibly beacuse companies and associates are memo fields?

If Not IsNull(Me.txtSearch) Then
strWhere = strWhere & "(([Name] Like ""*" & Me.txtSearch & _
""*") OR ([Associates] Like ""*" & Me.txtSearch & _
""*") OR ([Companies] Like ""*" & Me.txtSearch & ""*"))"
End If

I have a range of unbound textboxes that operate as search boxes. I
would
like to type a name (whether this be full, first or surname) in to the
box
and then the database searches for a record where that name appears in
ANY
one of those 3 fields. The same data does not exist in these fields but
related data does

I am unsure about whether I can do this using Allen Brownes serach form
tutorial. The code I have used is below and whilst this will search the
one
field I have mentioned, if I use the Or function it does not work.
Could
anyone help or at least point me in the right direction.

If Not IsNull(Me.txtSearch) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtSearch & ""*")"

End If

The other two fields I wish to search [Associates] and [Companies]

 

[rjw24]

I copied and pasted and it didnt work either. It appears to be cutting the
last bit of the search off. The error says that you cannot apply the argument
to the object.

The below was in the immediate window

([Name] Like "*nichols*") OR ([Associates] Like "*nichols*") OR ([Companies]
Like "*nicho

Sorry: I just copied'n'pasted yours. Quotes are wrong:

strWhere = strwhere & "(([Name] Like ""*" & Me.txtSearch & _
"*"") OR ([Associates] Like ""*" & Me.txtSearch & _
"*"") OR ([Companies] Like ""*" & Me.txtSearch & "*""))"

For debugging, add the line:
Debug.Print strWhere
just after the above. If it fails, open the Immediate Window (Ctrl+G) and
see what came out.

For details on how the quotes should work, see:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for your help.

That didnt seem to work either, I got a "type mismatch error" - is that
possibly beacuse companies and associates are memo fields?

If Not IsNull(Me.txtSearch) Then
strWhere = strWhere & "(([Name] Like ""*" & Me.txtSearch & _
""*") OR ([Associates] Like ""*" & Me.txtSearch & _
""*") OR ([Companies] Like ""*" & Me.txtSearch & ""*"))"
End If

I have a range of unbound textboxes that operate as search boxes. I
would
like to type a name (whether this be full, first or surname) in to the
box
and then the database searches for a record where that name appears in
ANY
one of those 3 fields. The same data does not exist in these fields but
related data does

I am unsure about whether I can do this using Allen Brownes serach form
tutorial. The code I have used is below and whilst this will search the
one
field I have mentioned, if I use the Or function it does not work.
Could
anyone help or at least point me in the right direction.

If Not IsNull(Me.txtSearch) Then
strWhere = strWhere & "([Name] Like ""*" & Me.txtSearch & ""*")"

End If

The other two fields I wish to search [Associates] and [Companies]

 

[Allen Browne]

So it's chopping off the last few characters.

Any chance you have something after this that chops off some characters,
such as a trailing " AND " that's supposed to be there?

 

[rjw24]

Sorted - I'd missed an AND of the end - The code in full is below.

If Not IsNull(Me.txtSearch) Then
strWhere = strWhere & "(([Name] Like ""*" & Me.txtSearch & _
"*"") OR ([Associates] Like ""*" & Me.txtSearch & _
"*"") OR ([Companies] Like ""*" & Me.txtSearch & "*"")) AND "

End If

Thanks!

So it's chopping off the last few characters.

Any chance you have something after this that chops off some characters,
such as a trailing " AND " that's supposed to be there?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I copied and pasted and it didnt work either. It appears to be cutting the
last bit of the search off. The error says that you cannot apply the
argument
to the object.

The below was in the immediate window

([Name] Like "*nichols*") OR ([Associates] Like "*nichols*") OR
([Companies]
Like "*nicho