Clean address info via query? (1 Viewer)

[gojets1721]

I have this import tool which allows the user to pull in thousands of lines of customer data via a excel import. The problem is that the address data is all wonky. There's no consistency at all in terms street type. For instance, 'road' could be 'Rd", "Rd." or "road". This data is exported from our CRM and the CRM folks stated they have absolutely no intention to clean this up on their end lol

Is there a way for access to clean this up via an access query after its imported? Like automatically change all instances to preset formats such St., Rd., Ave., etc.? Thanks

 

[theDBguy]

Hi. You could start with using an UPDATE query. Check out the Replace() function.

 

[gojets1721]

Okay. Is it possible to include multiple replace functions? Cause I would need to do it for each street type (st, rd, ave, etc)

 

[theDBguy]

Okay. Is it possible to include multiple replace functions? Cause I would need to do it for each street type (st, rd, ave, etc)

Yes, you can "nest" the function calls.

 

[gojets1721]

so essentially, this would be for fixing "Street" types with the intention of just making them all "ST":

Replace(Replace("Street", "Street", "ST")), "St.","St.","ST")

 

[theDBguy]

so essentially, this would be for fixing "Street" types with the intention of just making them all "ST":

Replace(Replace("Street", "Street", "ST")), "St.","St.","ST")

Maybe more like.

Replace(Replace([BadAddress], "street", "ST"), "st.", "ST")

 

[The_Doc_Man]

If you are going to do this very often, consider instead to have a series of queries with REPLACE functions. There is nothing wrong with the idea of running a macro that executes a sequence of RunQuery actions. If you have many such "fixes" to make and you are going to do it often, you might get better results by automating the process. If this is a one-off then it makes less sense to do it this way.

 

[Pat Hartman]

You could make a function and use that in your append query. You can only clean up one field with each function. but you could use the function with an argument to return some part. For example, if you wanted to split a full name field, you could call the argument twice and return last name the first time and first name the second

Select SplitName(FullName, 1) as FirstName, SplitName(FullName, 2) as LastName

 

[arnelgp]

see this demo.
open Imported table.
close the table.
run Query1.
open the table again.

view the query in design view.
view the function in module1.

 

[gojets1721]

see this demo.
open Imported table.
close the table.
run Query1.
open the table again.

view the query in design view.
view the function in module1.

This did the trick!! Thank you so much!!!

 

[RogerCooper]

so essentially, this would be for fixing "Street" types with the intention of just making them all "ST":

Replace(Replace("Street", "Street", "ST")), "St.","St.","ST")

Be careful with this. You don't want to St. Louis Avenue to Street Louis Avenue. Or change "Stanley Road" to "Streetanley Road". You should be using spaces in front and behind so " ST " become " STREET "

 

[gojets1721]

see this demo.
open Imported table.
close the table.
run Query1.
open the table again.

view the query in design view.
view the function in module1.

I'm actually getting a 3052 error stating that the file sharing lock count exceeded. The debugger is keying on this line in your example file's module:

Set rst = dbs.OpenRecordset(SQL, dbOpenSnapshot, dbReadOnly)

Any idea how to fix this?

 

[RogerCooper]

Try to see if you can you make the changes by hand using a select query. This may be an edit rights issue.

 

[gojets1721]

Yes I can. I suspect the issue is the size of file that I'm importing, which is then updated using @arnelgp's module and query. When I got the error message, the import was about 200,000 entries. When I reduced the import to 100,000, the module and code worked just fine

Any help someone can provide is greatly appreciate, because users will be importing these large files on average

 

[RogerCooper]

This sort of issue is backend specific. Personally, I would hesitate to change more than 10,000 records at once.

My earlier suggestion about being careful had an error in them (proving my point). You need to constrain the records for some queries. " ST" is OK in the middle of the field but not the end. You don't want to change "FIRST ST" to "FIRSTREET STREET".

 

[jdraw]

You can review the accepted street types by your postal authority, then create a table of these and the anticipated "misspellings". You could then process the incoming street address info and replace street type with the "authoritative" street type (postal authority value). If any of the misspellings, then replace with correct term.
As Roger said make sure you test your algorithm on some extreme samples. The St. Louis example is typical of what can happen. Make sure you put some spaces around your St. or Road when checking so you avoid St. Louis, St Cloud etc.
Seem unfortunate that the CRM isn't interested in cleaning the data, but we don't know your organization, its data management philosophy

 

[gojets1721]

This sort of issue is backend specific.

Any idea how to fix it? If its not to crazy, I'd like to do it. The module and query that @arnelgp suggested works great. Except when the import is large

 

[Pat Hartman]

I didn't look at arnel's code but I agree with Roger. When I have to do this, I break the pieces up first using split. then use the replace on only a specific part of the string. Usually the last section.

 

[Isaac]

I didn't look at anyone's code, but the error you're getting just reminds me that it's very important to close dao recordsets and, rarely for this issue, also important to set them to nothing. They can cause problems if they are allowed to "close" by simply going out of scope - and if that code runs 10's or 100's of times. Check for that.

 

[arnelgp]

file sharing lock count exceeded

i made changes to the module.