Address Field Cleanup

I have two fields "address" and "plaintiff address" in table jpdat

Sample Data                           expected outcome "address"    expected outcome "plaintiff"

123 Main Street                     123 Main St                                123 Main St
123 Main St #21                    123 Main St ,21                           123 Main St unit 21
123 Main Street #21             123 Main St ,21                            123 Main St unit 21

I have created two other tables that have fields "badstring" "goodstring" in tables "Address_Correction" and "Pltf_Address_correction"

Example Data
Bad String        Goodstring
Street               St
Str                    St
Avenue           Ave  

Etc.....

First I am a novice at Access 2000
1. I am looking to clean up addresses in a form based on JPDAT table, using the information in the prespective  correction tables. How can I do this? The Address field and the PltfAddress Field need slightly different cleanup. that is why I have two correction tables.

2. if the end of the address such as 123 main st 21 is numbers I want to transfer that into the apartment field. Can I do this based on if there is a street type and if there is not one present.

Thanks for your help in advance

All help would be appreciated.


cskehanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
Find & Replace Utility  at http://www.rickworld.com
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
or try this from matthewspatrick


http://vbaexpress.com/kb/getarticle.php?kb_id=841
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
DO use Cap's idea.  I can vouch.  I've used F&R for 14 years. Never fails.  I beta test all new version release. Do it!

mx
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

cskehanAuthor Commented:
Ok I am a novice How do I adapt Capricrorn's idea in my database
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Which one ?

mx
0
cskehanAuthor Commented:
0
Rey Obrero (Capricorn1)Commented:
there is an instruction from the web page, just follow it
0
cskehanAuthor Commented:
OK I Switched to the r and f but I can't get it to replace anything am I missing something? I installed like it said I put in my table name and told it what to find and what to replace it with. It tells me search complete but it did not change anything. Any Suggestions??
0
cskehanAuthor Commented:
oops that is rickworld.com one
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
F&R:

Enter the Find
Enter the Replace
Click the Tables, Queries, etc buttons
Check (mark) Log/FindChanges
Click Find.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
When the search is complete, click 'View Log'
Also .... don't check 'Replace With Asking', otherwise you will not get prompted for the changes ... unless that is explicitly what you want.

let me know ...

mx
0
cskehanAuthor Commented:
ok did that still no changes occured log looks like this
Find and Replace Log      Database: C:\Documents and Settings\HP\My Documents\Dallas
      Evictions\Dallas2000.mdb
04-Apr-07
      Doc      ElementName.Property      Replaced?      Old Value      New Value
Session Time Stamp = 4/4/2007 5:17:41 PM
Find/Replace Text Specifications for this search:
      FindSpec              LANE      LN
      FindSpec              STREET      ST
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Under this:

Find/Replace Text Specifications for this search:
      FindSpec              LANE      LN
      FindSpec              STREET      ST

There s/b:
Searched In:
searchin                                       > and here s/b what was searched,eg Tables (like*), (Queries like*) ...etc

I don't see that ???

mx
0
cskehanAuthor Commented:
that is what I was thinking maybe I should uninstall then reinstall
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Humm .... well, it's just an MDE and INI file ...that's it.  The INI file s/b in the Windows  (root) folder

repl9.ini

Try searching something really simple ... maybe a field name in one table and/or query .... with just Tables and Queries buttons pushed ...

mx
0
cskehanAuthor Commented:
uninstall and reinstall did not work. I guess I have to wait for registration number to get results.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
oooooh !!!

Humm .... I though trail version worked of x # of days ??

Really ... F&R WORKS ... flawlessly ... I use it daily ...

Can you post your mdb to http://www.ee-stuff.com/Expert/Upload/upload.php - removing any sensitive data?

I could run a test to be sure ...

mx

0
cskehanAuthor Commented:
on its way
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I need the link ee-stuff give you upon successful upload ...

Also ... give me some couple of exact things to search on  ... be specific ... any spaces, etc ...

mx
0
cskehanAuthor Commented:
got another way keeps telling me my file is too small
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
There are some issues with EE stuff.  At the top ('Question'), paste in the entire url to this thread instead of the 'ID'
  Also, be sure it's a zip file  and it can't be larger than 4mb.

Sorry, not other way ...

mx
0
cskehanAuthor Commented:
ok I finally got the program to work...but... I only want it to change values in the fields not any of the table properties such as field names. Please advise I will give example


From Address                              To Address
123 main street # 124                  123 main st ,124
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ...

Bring up F&R.
Click the check box 'Search user-specified properties'. This will open a new window.
From the 'Property Applies To:" drop down, select 'TableFieldValue'
In the box below that, add the name of the Table and the Field using the syntax:
    TableName.FieldName
Do this for all the tables/fields you want to search.
This is one of the very cool features of F&R ..

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.