Concatenate addresses (Acc97-SR2)

Topic

New Reply

Data was imported into a new database via a MS-Word file converted to a tab (switched from paragraph)-delimited list.
Unfortuantely, in the confusion this data was strewn into as many fields as 6 for addresses alone.

How can I concatenate this list (see attachment) back into it’s respective fields of [ Address] [City] [State] and [ZIP]?

Reply | Quote

Replies

try
[field1] & ” ” & [field2]there is a space between the ” ”
this puts a space between the fields
you can use this in the labels also

Reply | Quote

Yes, I can sew them all together into long rambling strings,
but I want to ‘glean’ information such as city in one field, state, and zip in others.

Using deductive reasoning…I could take the long string of concatenated goo
Search backwards through it and take the first space or non-numeric character
besides “-” (as in, 12345-6789) which would be an extended zip code, and call that ZIP.

Take the remainder (StrPos)and take off the next two chars
which were preceded by a space and call that “State”.

The rest would be all address…
City will be a bear…because no city has a defined number of words.

I saw a brilliant piece of function calling via SQL which passes each argument to the function,
searches the string and if true trips the switch
but that was just Boolean. I need string parsing.

Make sense?
It’s maddening…I LOVE IT!!!

Reply | Quote

Sorry, but you’re going to have to concatenate all the fields into a single string before you can parse bits of it out. I prefer to use a semicolon for this rather than a space, since semicolons rarely show up in addresses naturally, while other punction may. I did something like this at one time with names of varying lengths. If you start with a semicolon and end with a semicolon and put semicolons between each field, whether or not it has contents, you’ll know eactly how many pieces of data you have to deal with and you can start from the last piece and move to the first. Since the last piece will be alph-numeric for a postal code or straight alpha for a state or province, you can sort those two out fairly easily. The rest, you’ll have to figure out for yourself. If did this by parsing the string to elements of an array and then examining each element to determine what it was before I assigned it to a new field. Obviously, you can’t do this with a query, it will take code.

Reply | Quote

I knew that I’d have to concatenate them, I was more or less probing to see if this had been encountered before. I believe I read that Access97 doesn’t have’ InStrRev’, so I imagine the old method of counting the delimiters in each string would be the best approach.

If the last series ends with numeric then the next series must have 2 letters (State)
That at least potentially takes care of those two fields.
Thanks for the semicolon tip.
That’ll prove useful.

Reply | Quote

I found this code somewhere and while I understand it, I keep failing to open the table with it.
How would I plug in a table from here(myTable) and make Strinname check the field address?

Reply | Quote

Sorry, but I don’t understand your question. The code you posted opens a recordset, apparently based on a table, in this line:

Set RS = DB.OpenRecordset(“TableTest”, dbOpenDynaset)

TableTest appears to be the table name. If you plug in your table name instead, are you NOT able to open the recordset? All the code is doing is populating a dynamic array from the recordset. What did you want to do with the array (or the recordset) once you have it?

Reply | Quote

I created this temp table so I could burn new fields into the table with the parsed data.
My next step was to run a series of loops testing the string for datatype.
I could test the last string to see if it’s either 5 or 10 chars in length and alphanumeric.
If true it’s more than likely a zip code.
If the zip code tested true…
The next two letters would have to be the state (Uppercase, preceded and followed by a space)
The city would be the opposite..alpha only (Could contain several words.)
The address would be similar to the zip but a tougher test.

When I finally looked in the debug window, I discovered that it actually was working.
It is however, looping over the first record.

Since the code I ‘borrowed’ dealt with a Form,
I had a feeling that getting the desired results would take some tinkering.

Would I utilize the For-Next(i) UBound segment to not only reference the array elements, but number the new fields?

Reply | Quote

Here’s the reason it’s skipping an index:

    Do Until intSpacePos = 0
        ReDim Preserve StrTemp(UBound(StrTemp) + 1)
        StrTemp(UBound(StrTemp)) = Trim$(Left$(StrInName, intSpacePos - 1))
        StrInName = LTrim$(Right$(StrInName, Len(StrInName) - intSpacePos))
        intSpacePos = InStr(StrInName, ":")
    Loop

Since you already redimmed the array before entering this loop in this line …

ReDim StrTemp(1) As String

… it means that as soon as you enter the loop, the Ubound immediately gets changed to 2 and the array starts getting populated at that index. You can fix the problem by changing the original line to read like this:

ReDim StrTemp(0) As String

That way, the Ubound will get set first to 1, not 2, when your code enters the loop. You’ll have a zero index you never use or call, which is untidy, but it isn’t critical either.
[indent]

---

Would I utilize the For-Next(i) UBound segment to not only reference the array elements, but number the new fields?

---

[/indent]Sorry, but you lost me. What do you mean, number the new fields? Do you mean you want to use the same indexes on the fields collection as well? If the fields are in the same order as the elements you want to shove into them you can. It would mean you would never handle the first field in the fields collection, though, because your indexes start at 1. If the first field is an autonumber, you wouldn’t do anything with it anyhow, and it won’t make any difference.

Reply | Quote

You’ll have a zero index you never use or call, which is untidy, but it isn’t critical either.

Even if I have an Option Base 1 declared?

Would I utilize the For-Next(i) UBound segment to not only reference the array elements, but number the new fields?

Forget that. I didn’t know what I was talking about?

Reply | Quote

Yes, because you’re Redimming the array with 0 the first time in order to stop it from skipping index 1. Option Base sets the default LBound in a module but it can be overridden.

Reply | Quote

To get it to loop through the entire recordset, you need to move the line
Do Until RS.EOF right after the line
Set RS = DB.OpenRecordset(“TableTest”, dbOpenDynaset)
The problem is you move to the next record in the table but you don’t reset the values for Field15 and StrInName.
HTH
Paul

Reply | Quote

Where is the best place in the script to test for potential values?

– For zip code, could I declare two constants (a 5 and a 10 char value)
Then test with XOR?

– Would I first do yet another inStr and find the “-” in the string, if the element= 10 digits?

– Where do I start testing these elements?

– A For each (for each) element created in the array?

– Does any of this make sense?

I’m learning, albeit verrrrry slowly

Reply | Quote

Is it possible that you might be better off going back to the original source in Word and trying something there? I have had to do something like this for a client, but I was able to do my own export from Word. That made a huge difference.

Is the Word source still available?

Reply | Quote

If it could only be that easy.
Unfortunately, those files are long gone.
…and no one kept an archive of those files.

Reply | Quote

how many records are there involved ?
i recently got a load of data like this so wrote a quick procedure to make a best guess at the address, working backwards through the address as you mention, but then i got it passed over to a temp to get it typed up properly. As there were only a few hundred rows affected and it is cheeper to pay a temp to do a few days work than to worry about a coded solution which will still need human choice for some records anyway.

just a thought !

Reply | Quote

Many of my managerial friends have told me to do this.
I just figured it was because they were ‘less than technically inclined’,
now, I know it’s because they know better than to get bogged down with minutia.

There are a total of 992 rows…
Technically, if I screen out all of those that do not require complete addresses for mailing
(i.e. Departments or Divisions) we’re back down to 500 or so…

Reply | Quote

I’m sure that even a mediocre typist could enter 500 records before we’d get this one parsed out. It would be fun to work on but in the end there would still be manual work to sort everything out and do a double check for accuracy. I’m happy to work on it if you want to continue. The one question I have right now is what your current table records look like. If you could post one record so i could see the delimiters, I’ll put some time into it. You won’t lose anything and we both might learn something.
Paul

Reply | Quote

As rerquested:

The semicolon delimits each field
It ‘:’ starts and completes the string

Here’s 3 such samples..

:CST:RA 200:::
:GREENHORNE & O’MARA INC:5000 RITTER ROADSTE102:MECHANICSBURG PA 17055::
:NEW JERSEY DEPT OF TRANSPORTATION:BUREAU OF ENVIRONMENTAL ANALYSIS:1035 PARKWAY AVENUE CN 600:TRENTON NJ 08625:

Reply | Quote

Well if we want to take this a little at a time, I started with this.
I have a table that has all the 5 digit zipcode in this country, I can’t exactly remember where I downloaded it but it shouldn’t be hard to find it. Then I wrote this code that looks for the 5 digits in a string, and uses the zip table to insert the City, State Abbreviation, and 5 digit Zip into seperate fields in a Table. Don’t laugh at the Table names. It helps me keep things straight. It looks like this.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rsZip As DAO.Recordset
Dim rsNewTable As DAO.Recordset
Dim strZip As String
Set db = CurrentDb
Set rst = db.OpenRecordset(“tblParseHardString”, dbOpenDynaset)
Set rsZip = db.OpenRecordset(“tblZipCode”, dbOpenDynaset)
Set rsNewTable = db.OpenRecordset(“tblNewTable”, dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
rsZip.MoveFirst
Do Until rsZip.EOF
strZip = rsZip!Zipcode
If InStr(1, rst!ParseString, strZip) > 0 Then
rsNewTable.AddNew
rsNewTable!City = rsZip!CityName
rsNewTable!State = rsZip!StateAbbr
rsNewTable!Zip = rsZip!Zipcode
rsNewTable.Update
Else
End If
rsZip.MoveNext
Loop
rst.MoveNext
Loop

End Sub

It works well on my limited recordset. There is one issue with two towns of different names sharing a zip code. But you could use it to parse the State and Zip pretty flawlessly if that was something you want to consider. That’s as far as I’ve gotten today but I’ll have some time this afternoon to continue.
Paul

Reply | Quote

I found the zipcode database, and it is resting comfortably in it’s own table.
I copied the script (hopefully) but can’t deal with a null somewhere.

I know that variants won’t take nulls but I’m not trained in how to correct this.
The Help files are helpful if you want to debug or insert an NZ function in a form.
I DO NOT.

If I used these objects(whatever), how could I write the matching values to the fields?
Why can’t I just create a new field and insert them there…in the matching record…where the names are?

Set db = CurrentDb
Set rst = db.OpenRecordset(“finalTABLE8”, dbOpenDynaset)
Set rsZip = db.OpenRecordset(“tbl_city”, dbOpenDynaset)
‘ I don’t want this part… Set rsNewTable = db.OpenRecordset(“tbl_NewTable”, dbOpenDynaset)

Here’s an attachment of my script that tripped.
I appreciate any replies and all patience.

Reply | Quote

Actually, variants are the only variable that *does* accept nulls. However, that isn’t the situation here. All the fields in a new record are going to be null until you populate them. What you need to be looking at is the value in rsZip!CityName. If that’s Null, then you have a problem. What your code is doing is adding a new record and then stepping through the fields in that record and setting the value of each field to the value that’s in that field in the other recordset. If you have missing values, you can test for that and skip that operation by doing something like this:

If Not IsNull(rsZip!CityName) Then
    rsNewTable!City = rsZip!CityName
End If

Then if the CityName value is null in the existing recordset, it won’t try to add it to City in the new table. You would normally do it this way rather than using Nz unless you want a default value to be inserted, maybe something like “UNKNOWN”. You don’t want to insert an empty string into the field because that will bite you later and may not be legal if you have the Allow Zero Length property set to No.

Reply | Quote

The cold reality of brawn over brain !!
If this situation is likely to occur again then it may well be worth us going through it as it could prove to be a real get out of jail process to show off to a user who comes up with some ‘urgent’ job from nowhere.

I’d be interested to see the code you have so far and a data sample too, if possible.

Reply | Quote