asked on

split a field using an array

I have a table called customers in which i need to split the address field into four parts streetnumber streetname unittype and unit

so that given becomes
address street number streetname unittype unit
123 main unit 12 123 main unit 12
123 main #12 123 main # 12
123 main lot 12 123 main lot 12
123 main ste 12 123 main ste 12
123 main 123 main

all addresses that have some form of unit number are at the end of the address.
I need to also remove the transferred data from the orginal field.
I can seperate the streetnumer with no problem leaving the street name and unit number, but I am not sure how to process the array.
Please aid me in the best way possible to accomplish this feat.

Rey Obrero (Capricorn1)

unfortunately, you can not use use split in a query.

where and how do you want this done?

dim vAddress,streetnumber,streetname, unittype, unit
vaddress=split(address, " ")

streetnumber=vaddress(0)
streetname=vaddress(1)
unittype==vaddress(2)
unit==vaddress(3)

Jez Walters

You could consider a hybrid VB/SQL solution, using the folowing query:

SELECT StreetNumber(YourAddressField) AS StreetNumber, StreetName(YourAddressField) AS StreetName, UnitType(YourAddressField) AS UnitType, Unit(YourAddressField) AS Unit
FROM customers

and then create the following funcions:

Option Explicit
Option Compare Binary
Option Base 0
 
Public Function StreetNumber(pstrAddress As String) As Variant
    On Error Resume Next  ' Ignore subscript out of range
    StreetNumber = Null
    StreetNumber = Split(pstrAddress)(0)
End Function
 
Public Function StreetName(pstrAddress As String) As Variant
    On Error Resume Next  ' Ignore subscript out of range
    StreetName = Null
    StreetName = Split(pstrAddress)(1)
End Function
 
Public Function UnitType(pstrAddress As String) As Variant
    On Error Resume Next  ' Ignore subscript out of range
    UnitType = Null
    UnitType = Split(pstrAddress)(2)
End Function
 
Public Function Unit(pstrAddress As String) As Variant
    On Error Resume Next  ' Ignore subscript out of range
    Unit = Null
    Unit = Split(pstrAddress)(3)
End Function

Open in new window

Jez Walters

By the way, in your question why does the address "123 main #12" give a unittype of "#"?

What separator are you using - space or hash?

Rey Obrero (Capricorn1)

jez, that is obvious { space }
jez, you might want to add the delimiter in your function

Public Function Unit(pstrAddress As String, sDel as variant) As Variant
On Error Resume Next ' Ignore subscript out of range
Unit = Null
Unit = Split(pstrAddress,sDel)(3)
End Function

Connie Jerdet-Skehan

ASKER

ok jez it doesn't work if the address is 123 main st unit 12

Jez Walters

You need to explain your parsing rules - how are you splitting up your address into it's various components? The solution I posted uses space delimiters, although this obviously isn't what you've used in the second record of your question (hence my comment).

Similarly, your last posting has 5 fields separated by spaces - if this isn't what you expect, you'll have to clarify what constitutes an address field.

Connie Jerdet-Skehan

ASKER

I guess the best way to explain is first I want to seperate the street number from the address then I want to seperate the apt 124 or lot 124 etc from the remaining I can seperate the rest.

Jez Walters

No offense intended, but I'm afraid this is not a clear explanation of your parsing rules - you need to specifiy exactly how you determine ALL of the address fields you wish to extract.

Perhaps some more examples would be helpful?

Connie Jerdet-Skehan

ASKER

ok all I want to do is 1st parse the address number then parse the apt or unit number like this so that

123 main st #21
123 main st
345 daniel boone unit 21
645 olga lane lot 16

becomes
Street number street name unit
123 main st #21
123 main st
345 daniel boone unit 21
645 olga lane lot 16

I will then take the #, unit, lot in unit and transfer it to a field called unittype .
Not all addresses have a unit or apt number
the unit number is always at end of the field but does not always have a space between the unit indicator and unit number. Like #21.

Jez Walters

The street names in your latest sample don't seem to quite tie up with the street names in your original question.

Before proceeding, can you confirm that the street number is ALWAYS present (the 1st word) and that the street name is always two words (the 2nd and 3rd words)?

By 'word' I mean a string of characters delimited by spaces or the beginning/end of the string!

Connie Jerdet-Skehan

ASKER

ok the streetnumber is obvious it is always at the beginning of string followed by a space. First Word I want to seperate from string place in field called streetnumber and remove it from the original string.

so that would become
address address Streetnumber
123 main st apt 12 main st apt 12 123

After this is accomplished I wish to seperate the unit,apt,lot,space number from the remainder of string and place it in a field called Unit and remove it from the orginal string. Keep in mind unit numbers are always at end if there is one and the only way to seperate them is to create a const or array using apt,lot,unit,#,ste,suite as the information to look for.

So taking what we have left after the streetnumber is removed.

I need this to become this
Address address unit
main st apt 12 main st apt 12 (if possible removing the array after transfer)

I hope this explains what I am looking for. TY for your patience.

Connie Jerdet-Skehan

ASKER

one more thing the the unit field may already have information in it from other addresses that do not conform to the above mentioned criteria. So when the loop is done I only want to update the unit field if it is null.

Jez Walters

Sorry for not getting back to you earlier (mucho busy), but how's this:

Dim astrAddress() As String
Dim lngCurrentField As Long
Dim lngLastField As Long
Dim strStreetNumber As String
Dim strStreetName As String
Dim strUnitType As String
Dim strUnit As String
 
astrAddress = Split(Replace(pstrAddress, "#", "# "))  ' Treat # as a separate word
strStreetNumber = astrAddress(0)
strUnitType = astrAddress(UBound(astrAddress) - 1)
Select Case strUnitType
    Case "apt", "lot", "unit", "ste", "suite"
        lngLastField = UBound(astrAddress) - 2
        strUnit = strUnitType & " " & astrAddress(UBound(astrAddress))
    Case "#"
        lngLastField = UBound(astrAddress) - 2
        strUnit = strUnitType & astrAddress(UBound(astrAddress))
    Case Else
        lngLastField = UBound(astrAddress)
End Select
For lngCurrentField = 1 To lngLastField
    strStreetName = strStreetName & astrAddress(lngCurrentField) & " "
Next
strStreetName = Trim(strStreetName)
 
MsgBox "Address: " & pstrAddress & vbCr & vbCr & _
       "Street number: " & strStreetNumber & vbCr & _
       "Street name: " & strStreetName & vbCr & _
       "Unit: " & strUnit

Open in new window

Jez Walters

Sorry, you might want the subroutine definition too!

The key here is to replace "#" with "# " before parsing, so that all unit types can be treat as a separate word.

Public Sub ParseAddress(pstrAddress As String)
    Dim astrAddress() As String
    Dim lngCurrentField As Long
    Dim lngLastField As Long
    Dim strStreetNumber As String
    Dim strStreetName As String
    Dim strUnitType As String
    Dim strUnit As String
 
    astrAddress = Split(Replace(pstrAddress, "#", "# "))  ' Treat # as a separate word
    strStreetNumber = astrAddress(0)
    strUnitType = astrAddress(UBound(astrAddress) - 1)
    Select Case strUnitType
        Case "apt", "lot", "unit", "ste", "suite"
            lngLastField = UBound(astrAddress) - 2
            strUnit = strUnitType & " " & astrAddress(UBound(astrAddress))
        Case "#"
            lngLastField = UBound(astrAddress) - 2
            strUnit = strUnitType & astrAddress(UBound(astrAddress))
        Case Else
            lngLastField = UBound(astrAddress)
    End Select
    For lngCurrentField = 1 To lngLastField
        strStreetName = strStreetName & astrAddress(lngCurrentField) & " "
    Next
    strStreetName = Trim(strStreetName)
 
    MsgBox "Address: " & pstrAddress & vbCr & vbCr & _
           "Street number: " & strStreetNumber & vbCr & _
           "Street name: " & strStreetName & vbCr & _
           "Unit: " & strUnit
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Jez Walters

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jez Walters

You may as well have the attached sample database too, in case it helps:
AddressFields.txt

Connie Jerdet-Skehan

ASKER

I am getting runtime error 94 invalid use of null.

Jez Walters

Whereabouts are you getting this error?

An educated guess (you'll have to confirm this) is that your Unit field is not in actually optional - contrary to your post ID 20887674 above.

Jez Walters

How are you getting on with resolving this question?