complex criteria to find one record using wildcards?

  • Thread starter Thread starter Elizabeth
  • Start date Start date
E

Elizabeth

I am trying to create a query that would find one location based on an
address. I can get the query to work as long as the criteria is entered
exactly as it is entered in the underlying table, but I need to account for
misspellings (ex. road, rd, rd., etc.). I know that wildcards can be used if
you do a new query each time, but the users will not be doing a query. They
will be putting the criteria in when they run a report. Is there any way to
set this up?
 
Access cannot handle misspellings (ex. road, rd, rd., etc.).
It can with the wildcards find based on partial entry like this --
1234 Willow Street using --
Like "*" & [Enter house number] & "*" & [Enter street name] & "*"
So if you type 1234 and Willow it
will pull the record.
It will also pull 9345 South Willow Drive
or 8976 Willow St. Apartment # 23
 
Thank you very much. I knew that it would possibly give extra answers, but I
couldn't figure out where to put the wild cards. I appreciate your help.

Elizabeth


KARL DEWEY said:
Access cannot handle misspellings (ex. road, rd, rd., etc.).
It can with the wildcards find based on partial entry like this --
1234 Willow Street using --
Like "*" & [Enter house number] & "*" & [Enter street name] & "*"
So if you type 1234 and Willow it
will pull the record.
It will also pull 9345 South Willow Drive
or 8976 Willow St. Apartment # 23

--
Build a little, test a little.


Elizabeth said:
I am trying to create a query that would find one location based on an
address. I can get the query to work as long as the criteria is entered
exactly as it is entered in the underlying table, but I need to account for
misspellings (ex. road, rd, rd., etc.). I know that wildcards can be used if
you do a new query each time, but the users will not be doing a query. They
will be putting the criteria in when they run a report. Is there any way to
set this up?
Click to expand...
Click to expand...
 
Matching addresses is more art than science. The most intractable cases are
numeric vs. textual street names, e.g. 1st Ave. vs. First Ave. One approach
is to process each address into a standardized form as it is entered. The
standardization needs to be done as a new address is entered into the
database, and for each user input when running a query. For an example, see
how the USPS standardizes addresses at http://zip4.usps.com/zip4/ Enter the
same address in several forms and see how it is transformed. Implementing
that is not a trivial exercise.
-TedMi
 
Back
Top