Solved

Address Field Cleanup

Posted on 2007-04-04
23
225 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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

623 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