using Like in search form

[Guest]

I have an Access 2003 database that tracks phone calls. I have created a
"search" form that has about 15 fields in it that allows the user to search
for a specific phone call based on any one or more of the fields.

All fields work just fine except for one. It's a text field that contains
key search words about the phone call. When I try to search on this field, I
need to be able to use the LIKE syntax because the user will obviously not
remember all the key search words. When I enter - like *accident*- for
example, it returns all records, even though only one call pertained to a car
accident.

The column in the query that runs the results is set up as follows:
((Forms!frmSearch!Regarding) or (Forms!frmSearch!Regarding is null))
to allow the user to leave certain fields blank if they so choose.
What do I need to do differently to let a user search on this field using
LIKE

 

[John Vinson]

I have an Access 2003 database that tracks phone calls. I have created a
"search" form that has about 15 fields in it that allows the user to search
for a specific phone call based on any one or more of the fields.

All fields work just fine except for one. It's a text field that contains
key search words about the phone call. When I try to search on this field, I
need to be able to use the LIKE syntax because the user will obviously not
remember all the key search words. When I enter - like *accident*- for
example, it returns all records, even though only one call pertained to a car
accident.

The column in the query that runs the results is set up as follows:
((Forms!frmSearch!Regarding) or (Forms!frmSearch!Regarding is null))
to allow the user to leave certain fields blank if they so choose.
What do I need to do differently to let a user search on this field using
LIKE

Change the criterion *for this field only* to

LIKE "*" & [Forms]![frmSearch]![Regarding] & "*"

If the user just types Accident in the textbox, it will search for all
records containing the string Accident anywhere within this field.

If the user leaves it blank the wildcards will insure that all records
are found (for this criterion).

John W. Vinson[MVP]

 

[Guest]

Thank you - that was way too easy! I was trying to make it much harder than
it needed to be

I have an Access 2003 database that tracks phone calls. I have created a
"search" form that has about 15 fields in it that allows the user to search
for a specific phone call based on any one or more of the fields.

All fields work just fine except for one. It's a text field that contains
key search words about the phone call. When I try to search on this field, I
need to be able to use the LIKE syntax because the user will obviously not
remember all the key search words. When I enter - like *accident*- for
example, it returns all records, even though only one call pertained to a car
accident.

The column in the query that runs the results is set up as follows:
((Forms!frmSearch!Regarding) or (Forms!frmSearch!Regarding is null))
to allow the user to leave certain fields blank if they so choose.
What do I need to do differently to let a user search on this field using
LIKE

Change the criterion *for this field only* to

LIKE "*" & [Forms]![frmSearch]![Regarding] & "*"

If the user just types Accident in the textbox, it will search for all
records containing the string Accident anywhere within this field.

If the user leaves it blank the wildcards will insure that all records
are found (for this criterion).

John W. Vinson[MVP]

 

[Guest]

I'm trying to do a similar thing but in Access 2000 and I'm not sure if that
makes a difference or not. I have 4 combo boxes on a form. The user needs
to be able to choose one of the selections, part of one, or none. After
completing the 4 criteria a query is run that uses these 4 for filtering the
data. My criteria in the query is :
Like "*" & [forms]![frmInvTransfer_main]![cbocellid] & " *" . This does
not return any records, I have also tried :
Like "*" & IIf([forms]![frmInvTransfer_main]![cbocellid] Is Null, "*",
([forms]![frmInvTransfer_main]![cbocellid])) Thsi gives me records only when
the exact choice is made or none. Help and I need to do this for 4 different
combo boxes!

Thank you - that was way too easy! I was trying to make it much harder than
it needed to be

I have an Access 2003 database that tracks phone calls. I have created a
"search" form that has about 15 fields in it that allows the user to search
for a specific phone call based on any one or more of the fields.

All fields work just fine except for one. It's a text field that contains
key search words about the phone call. When I try to search on this field, I
need to be able to use the LIKE syntax because the user will obviously not
remember all the key search words. When I enter - like *accident*- for
example, it returns all records, even though only one call pertained to a car
accident.

The column in the query that runs the results is set up as follows:
((Forms!frmSearch!Regarding) or (Forms!frmSearch!Regarding is null))
to allow the user to leave certain fields blank if they so choose.
What do I need to do differently to let a user search on this field using
LIKE

Change the criterion *for this field only* to

LIKE "*" & [Forms]![frmSearch]![Regarding] & "*"

If the user just types Accident in the textbox, it will search for all
records containing the string Accident anywhere within this field.

If the user leaves it blank the wildcards will insure that all records
are found (for this criterion).

John W. Vinson[MVP]