[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1089
  • Last Modified:

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.
0
cskehan
Asked:
cskehan
  • 11
  • 6
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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)
0
 
JezWaltersCommented:
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

0
 
JezWaltersCommented:
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?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Commented:
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
0
 
cskehanAuthor Commented:
ok jez it doesn't work if the address is 123 main st unit 12
0
 
JezWaltersCommented:
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.
0
 
cskehanAuthor Commented:
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.
0
 
JezWaltersCommented:
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?
0
 
cskehanAuthor Commented:
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.

0
 
JezWaltersCommented:
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!
0
 
cskehanAuthor Commented:
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.
0
 
cskehanAuthor Commented:
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.
0
 
JezWaltersCommented:
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

0
 
JezWaltersCommented:
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

0
 
JezWaltersCommented:
You can extend this to update your customers table, like this:
Dim astrAddress() As String
Dim lngCurrentField As Long
Dim lngLastField As Long
Dim rstRecordset As Recordset
Dim strUnitType As String
Dim varUnit As Variant
 
Set rstRecordset = CurrentDb.OpenRecordset("customers")
With rstRecordset
    Do Until .EOF
        .Edit
        astrAddress = Split(Replace(!Address, "#", "# "))  ' Treat # as a separate word
        !StreetNumber = astrAddress(0)
        strUnitType = astrAddress(UBound(astrAddress) - 1)
        Select Case strUnitType
            Case "apt", "lot", "unit", "ste", "suite"
                lngLastField = UBound(astrAddress) - 2
                varUnit = strUnitType & " " & astrAddress(UBound(astrAddress))
            Case "#"
                lngLastField = UBound(astrAddress) - 2
                varUnit = strUnitType & astrAddress(UBound(astrAddress))
            Case Else
                lngLastField = UBound(astrAddress)
                varUnit = Null
        End Select
        For lngCurrentField = 1 To lngLastField
            !StreetName = !StreetName & astrAddress(lngCurrentField) & " "
        Next
        !StreetName = Trim(!StreetName)
        If IsNull(!Unit) Then
            !Unit = varUnit
        End If
        .Update
        .MoveNext
    Loop
    .Close
End With

Open in new window

0
 
JezWaltersCommented:
You may as well have the attached sample database too, in case it helps:
AddressFields.txt
0
 
cskehanAuthor Commented:
I am getting runtime error 94 invalid use of null.
0
 
JezWaltersCommented:
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.
0
 
JezWaltersCommented:
How are you getting on with resolving this question?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now