Solved

Address Field Cleanup

Posted on 2007-04-04
23
222 Views
Last Modified: 2013-11-28
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.


0
Comment
Question by:cskehan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 3
23 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 18852602
Find & Replace Utility  at http://www.rickworld.com
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18852614
or try this from matthewspatrick


http://vbaexpress.com/kb/getarticle.php?kb_id=841
0
 
LVL 75
ID: 18852615
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:cskehan
ID: 18852807
Ok I am a novice How do I adapt Capricrorn's idea in my database
0
 
LVL 75
ID: 18852898
Which one ?

mx
0
 

Author Comment

by:cskehan
ID: 18852912
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18852934
there is an instruction from the web page, just follow it
0
 

Author Comment

by:cskehan
ID: 18854280
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
 

Author Comment

by:cskehan
ID: 18854284
oops that is rickworld.com one
0
 
LVL 75
ID: 18854339
F&R:

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

mx
0
 
LVL 75
ID: 18854350
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
 

Author Comment

by:cskehan
ID: 18854365
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
 
LVL 75
ID: 18854440
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
 

Author Comment

by:cskehan
ID: 18854456
that is what I was thinking maybe I should uninstall then reinstall
0
 
LVL 75
ID: 18854488
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
 

Author Comment

by:cskehan
ID: 18854647
uninstall and reinstall did not work. I guess I have to wait for registration number to get results.
0
 
LVL 75
ID: 18854674
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
 

Author Comment

by:cskehan
ID: 18854801
on its way
0
 
LVL 75
ID: 18854817
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
 

Author Comment

by:cskehan
ID: 18854856
got another way keeps telling me my file is too small
0
 
LVL 75
ID: 18854891
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
 

Author Comment

by:cskehan
ID: 18876837
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 18876916
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question