Text Search

[acss]

I am trying to create a paramater query that will result in only the records
from the word "customs" but if the user types in "customs fees" then the
records should only be from "customs fees". How do you create this type of
filtered search?

 

[John Spencer]

To return all records where the string exists any place in the field you
could use

Criteria: Like "*" & [What Word?] & "*"

If you want to match only if the exact word is included in the field
things get a lot trickier depending on how you define word and whether
or not your field can contain line feeds.

For instance, is the word custom in the following
"Today there are customary times where custom-induced behavior"

Match or not?
--customary includes the string custom but is not the word Custom
--custom-induced includes the string custom but custom is followed by a dash



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[acss]

I have the descriptions field as a drop down and end user can not edit the
descriptions. I just tried the this string but i continue to recive records
for both words "customs" and "customs fees". Any other suggestions to filter
one from the other?

To return all records where the string exists any place in the field you
could use

Criteria: Like "*" & [What Word?] & "*"

If you want to match only if the exact word is included in the field
things get a lot trickier depending on how you define word and whether
or not your field can contain line feeds.

For instance, is the word custom in the following
"Today there are customary times where custom-induced behavior"

Match or not?
--customary includes the string custom but is not the word Custom
--custom-induced includes the string custom but custom is followed by a dash



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I am trying to create a paramater query that will result in only the records
from the word "customs" but if the user types in "customs fees" then the
records should only be from "customs fees". How do you create this type of
filtered search?

 

[John Spencer]

Perhaps you can explain in more detail what you want. I don't
understand what you want to see returned if you enter "customs" or if
you enter "Customs Fees". What is the difference in what you want to
see returned? I am not even sure what field you are searching?

If you enter "Customs" do you want to exclude "Customs Fees"?

What is in the Descriptions?

Do you want to match the entire content of Descriptions?

Is Descriptions the field you are searching?

If you have a query that ALMOST works for you, post the SQL of the query
(Menu: View: SQL) and tell us what is wrong with the records that are
returned. Also tell us what your input was.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have the descriptions field as a drop down and end user can not edit the
descriptions. I just tried the this string but i continue to recive records
for both words "customs" and "customs fees". Any other suggestions to filter
one from the other?

To return all records where the string exists any place in the field you
could use

Criteria: Like "*" & [What Word?] & "*"

If you want to match only if the exact word is included in the field
things get a lot trickier depending on how you define word and whether
or not your field can contain line feeds.

For instance, is the word custom in the following
"Today there are customary times where custom-induced behavior"

Match or not?
--customary includes the string custom but is not the word Custom
--custom-induced includes the string custom but custom is followed by a dash



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I am trying to create a paramater query that will result in only the records
from the word "customs" but if the user types in "customs fees" then the
records should only be from "customs fees". How do you create this type of
filtered search?

 

[cttechsupport]

Well - unfortunately the nature of string search is going to work
against you here. A lesson learned about planning and testing before
committing to a running app.

If you MUST have both terms as searchable parameters you may have to
sacrifice the instring() function you are attempting to use (where you
accept the characters "customs" appearing anywhere in the string.
If ,as you imply, these entries are fixed and controlled by a combo
box you can run comparison queries on the COMPLETE string and not get
inaccurate results.

Meaning .........use the = test NOT the * & text & * test
the SQL is "Select * from [tablename] where [fieldname] = "customs")
or
"Select * from [tablename] where [fieldname] =
"customs fees")

Hope this helps........

Charlie

I have the descriptions field as a drop down and end user can not edit the
descriptions. I just tried the this string but i continue to recive records
for both words "customs" and "customs fees". Any other suggestions to filter
one from the other?

To return all records where the string exists any place in the field you
could use

Criteria: Like "*" & [What Word?] & "*"

If you want to match only if the exact word is included in the field
things get a lot trickier depending on how you define word and whether
or not your field can contain line feeds.

For instance, is the word custom in the following
"Today there are customary times where custom-induced behavior"

Match or not?
--customary includes the string custom but is not the word Custom
--custom-induced includes the string custom but custom is followed by a dash

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[acss]

I would like that if the user types in "customs" then that will be only the
records returned. For another example, i also have in the drop down "freight
inbound" and "freight outbound" so if the user types "freight inbound" in the
prompt then only those records should be returned excluding "freight
outbound" .Can this be done?

Perhaps you can explain in more detail what you want. I don't
understand what you want to see returned if you enter "customs" or if
you enter "Customs Fees". What is the difference in what you want to
see returned? I am not even sure what field you are searching?

If you enter "Customs" do you want to exclude "Customs Fees"?

What is in the Descriptions?

Do you want to match the entire content of Descriptions?

Is Descriptions the field you are searching?

If you have a query that ALMOST works for you, post the SQL of the query
(Menu: View: SQL) and tell us what is wrong with the records that are
returned. Also tell us what your input was.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have the descriptions field as a drop down and end user can not edit the
descriptions. I just tried the this string but i continue to recive records
for both words "customs" and "customs fees". Any other suggestions to filter
one from the other?

To return all records where the string exists any place in the field you
could use

Criteria: Like "*" & [What Word?] & "*"

If you want to match only if the exact word is included in the field
things get a lot trickier depending on how you define word and whether
or not your field can contain line feeds.

For instance, is the word custom in the following
"Today there are customary times where custom-induced behavior"

Match or not?
--customary includes the string custom but is not the word Custom
--custom-induced includes the string custom but custom is followed by a dash



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


acss wrote:
I am trying to create a paramater query that will result in only the records
from the word "customs" but if the user types in "customs fees" then the
records should only be from "customs fees". How do you create this type of
filtered search?

 

[John Spencer]

If you type in "Customs" and use like with wildcards then you will get
all records that have that string. There is really no way to exclude
records that have Customs Fees from appearing unless you include more
criteria to exclude certain words.

Field: Description
Criteria: Like "*Customs*" and Not Like "* Fees*"

On the other hand if you type in FREIGHT INBOUND only records with that
phrase will be returned - however if OUTBOUND is also in that record
then that record will be returned.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I would like that if the user types in "customs" then that will be only the
records returned. For another example, i also have in the drop down "freight
inbound" and "freight outbound" so if the user types "freight inbound" in the
prompt then only those records should be returned excluding "freight
outbound" .Can this be done?

Perhaps you can explain in more detail what you want. I don't
understand what you want to see returned if you enter "customs" or if
you enter "Customs Fees". What is the difference in what you want to
see returned? I am not even sure what field you are searching?

If you enter "Customs" do you want to exclude "Customs Fees"?

What is in the Descriptions?

Do you want to match the entire content of Descriptions?

Is Descriptions the field you are searching?

If you have a query that ALMOST works for you, post the SQL of the query
(Menu: View: SQL) and tell us what is wrong with the records that are
returned. Also tell us what your input was.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have the descriptions field as a drop down and end user can not edit the
descriptions. I just tried the this string but i continue to recive records
for both words "customs" and "customs fees". Any other suggestions to filter
one from the other?

:

To return all records where the string exists any place in the field you
could use

Criteria: Like "*" & [What Word?] & "*"

If you want to match only if the exact word is included in the field
things get a lot trickier depending on how you define word and whether
or not your field can contain line feeds.

For instance, is the word custom in the following
"Today there are customary times where custom-induced behavior"

Match or not?
--customary includes the string custom but is not the word Custom
--custom-induced includes the string custom but custom is followed by a dash



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


acss wrote:
I am trying to create a paramater query that will result in only the records
from the word "customs" but if the user types in "customs fees" then the
records should only be from "customs fees". How do you create this type of
filtered search?