Parse to remove end characters

Malcy

Registered User.
Local time
Today, 14:51
Joined
Mar 25, 2003
Messages
586
Hi
Can anyone show me how to remove the end characters from a string?
I made a big mistake in my earlier version of this database by concocting the address as a single string (OK I had my reasons but I can now see the error of my ways, and that proper normalisation is the true path to richeousness!)
I now recognise I would be better splitting it into its constituent parts, namely house number, street/town/postcode area, and then postcode sector.
I need, however, to convert existing data into my new three field structure.

I start off with something like
33-2 CLOVENSTONE PARK, ANYTOWN ZZ14 8PQ
I have managed to parse off the house number and also the last three characters which make up the postcode sector (although this still needs some manual editing since if there never was a sector there I end up with spurious data). I have also managed to cleave off the house number but I am left with
CLOVENSTONE PARK, ANYTOWN ZZ14 8PQ

I now need to remove the end blocks but cannot work out how. Trouble is sometimes there isnt a known postcode sector so my next record might be
CLOVENSTONE PARK, ANYTOWN ZZ14

What I need is to end up with all the records looking like
CLOVENSTONE PARK, ANYTOWN ZZ14
regardless of whether there was a postcode sector or not. I can then compare this to my street names table and allocate a numerical code for it and will hopefully end up with something like "33-2" "186" "8PQ" which will uniquely define the address.
Any ideas would be most helpfully received since the idea of manually doing this over several thousand records doesn't bear thinking about!!!
Thanks in advance
Best wishes

Malcy
 
Are you saying that you want to remove the 3 character (8PQ) sector code from the records that it exists on. If it's always 3 characters you can check for a space 4 characters from the end of the string...

First though, locate the comma and break the field into Street and Town:

Street = Left(MyField, InStr(MyField, ",") -1)
Town = Right(MyField, Len(MyField) - Len(Street) -2)

Then parse off the sector if it exists:

If Mid(Town, Len(Town)-3, 1) = " " Then
Sector = Right(Town, 3)
Town = Left(Town, Len(Town) -4)
 
Thanks RichO
Been trying to get my head around this since it is fairly baffling to begin with. I think your idea of locating town and street is better since the first part of the post code could be, for instance EH9. If I just looked for a space four characters from the right hand end it would think that this was the second part of the postcode rather than the second part.
I have tried to run your code but cannot get it to perform (I may well be trying to do it in the wrong place). I have built my data transformation sequence onto a form command button.
I dimensioned strStreet, strTown and strPc1 as strings then put the following code in, after a few cnn.execute lines to manipulate data:
Code:
' Open tblTransPtAddr and parse second part of postcode (if it exists) to separate field
    rst.Open "tblTransPtAddr", cnn, adOpenDynamic, adLockOptimistic
    With rst
        .MoveLast
        .MoveFirst
        Do While Not .EOF
            strStreet = Left(Street, InStr((Street), ",") - 1)
            StrTown = Right(Street, Len(Street) - Len(strStreet) - 2)
            StrPc1 = Right(Street, Len(Street) - (Len(strStreet) + Len(StrTown) - 3))
            
                If Len(Street) > (Len(strStreet) + Len(StrTown) + Len(StrPc1) + 3) Then
                    .Fields("Pc2") = Right([Street], 3)
                    .MoveNext
                End If
        Loop
    End With
The field holding the address, now less the house number which I have successfully parsed off and removed is called Street.
When I try running it as a step through it picks up all the preliminary data transformations but jumps to error as soon as it gets to the line defining strStreet saying it is an "invalid procedure call or action".
I am not sure if the logic of my If procedure is right but was hoping to verify as I watched the variables creating.
Any ideas what I am doing wrong?
Really appreciate your help with this since I have never tried to play around with strings this way before!
Best wishes

Malcy
 
When I try running it as a step through it picks up all the preliminary data transformations but jumps to error as soon as it gets to the line defining strStreet saying it is an "invalid procedure call or action".

Step through your code again and make sure there is actually a "," (comma) in the street field. If there isn't one in the string, "InStr((Street), ",") - 1" will return -1.
 
Thanks Doug
Tried that and in checking the data (currently all dummy but will be real data in live applications) there were two records without a comma. Don't know how!
Anyway I put comma into those two records in the right place, i.e. after the street name, and it still threw up the same error. So I guess it must be something else. Will keep trying!
Best wishes

Malcy
 
One idea you might want to contemplate is forcing all the data you are trying to parse into matching what the parsing function is going to expect.

IOW, if you don't have a comma in all the strings, put one there (like you just did).

If there isn't a postcode sector, add a dummy (like "___", 3 underscores).

Then when you run the parsing function, you can easily avoid updating with the dummy, and the parsing should work on all the records (I'd almost bet that now that error is occuring on one of the other string manipulation lines.)
 

Users who are viewing this thread

Back
Top Bottom