complex criteria to find one record using wildcards?

[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?

 

[KARL DEWEY]

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

 

[MGFoster]

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?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Educate the user to use wild cards. They can enter wild cards with
their search strings:

Address?: 123 Main St*

Address?: 567 Se*uia R*d

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSom+3IechKqOuFEgEQIJqQCfVChQEvZkE53Enw6HUvBwmSl6YiAAnRX0
9iS45EFR1a6NIb8u7g6AVfdw
=Oa8N
-----END PGP SIGNATURE-----

 

[Elizabeth]

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

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.

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?

 

[TedMi]

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