Search Multiple Fields From Same String

  • Thread starter Thread starter rjw24
  • Start date Start date
R

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
 
If Not IsNull(Me.txtSearch) Then
strWhere = strWhere & "(([Name] Like ""*" & Me.txtSearch & _
""*") OR ([Associates] Like ""*" & Me.txtSearch & _
""*") OR ([Companies] Like ""*" & Me.txtSearch & ""*"))"
End If
 
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?

Allen Browne said:
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.

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

rjw24 said:
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?

Allen Browne said:
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 said:
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]
Click to expand...
Click to expand...
Click to expand...
 
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

Allen Browne said:
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.

rjw24 said:
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?

Allen Browne said:
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]
Click to expand...
Click to expand...
Click to expand...
 
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?
 
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!

Allen Browne said:
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.

rjw24 said:
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
Click to expand...
Click to expand...
 
Back
Top