Solved

Address Field Cleanup

Posted on 2007-04-04
23
209 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
  • 10
  • 10
  • 3
23 Comments
 
LVL 119

Accepted Solution

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

Expert Comment

by:Rey Obrero
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
 

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 119

Expert Comment

by:Rey Obrero
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now