need module help in splitting a field

[gilli]

HI, I have been working on a module to split an address field into multiple columns. I am fairly knew to ms access but gained some knowledge in the past couple of days and I do have programming knowledge.
I have written a couple of functions to split an address field which has street address, city, zip, state all in a single field into multiple fields.
But I am not sure how to call this function in the query or design view and also how to create those new fields in the existing query or design view.
Hoping someone will guide me through this.
Thanks for any help.

 

[pbaldy]

What does the function look like? If it's written to accept a parameter, you'd call it like:

FunctionName("State")

Or however it expects the parameter.

 

[The_Doc_Man]

To make a function callable from a query, it must be in a general module and must be declared PUBLIC. It HAS to be a function since subroutines don't return a value.

Public Function SplitAddr( source-field-name, code-number) As String
... you said you had a method to return a value to SplitAddr even if it is an empty string, so select which sub-field to return based on your code number... that code goes here

    SplitAddr=the-answer
End Function


INSERT INTO other-table-name (HOUSENUMBER, STREETNAME, ....) 
SELECT SplitAddr(full-address-field, 1), SplitAddr(full-address-field, 2), ..., other-field-names FROM your-source-table-name ;

Something like that.

 

[gilli]

Thanks, Paul.
Here is my code snippet.

Public Sub aaaRunParser()
    Dim r As Boolean
    r = ParseAddresses
End Sub
Private Function ParseAddresses() As Boolean
'   Purpose: ParseAddresses - split hairy addresses
    On Error GoTo errorexit
    Dim result As Boolean
    result = False
    '================================
Dim db As DAO.Database
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Copy of Merge", dbOpenDynaset) 'source is a query
    'Set Torst = db.OpenRecordset("Table1", dbOpenDynaset)
    
    Do While Not rst.EOF
    '================================
        Dim addresses As String
        theName = rst.Fields("Full Name")
        addresses = rst!address
        SplitThem addresses
'        Debug.Print addresses
'================================
continue:
        rst.MoveNext
    Loop
    '================================

I have taken my query as source in the function and reading 'Address' field from there. Actually, address field is in json format, so there are a lot of apostrophes. I have other functions which clean up all those apostrophes and other extra spaces in the field before splitting.
Here are the functions to split and insert into new fields:

Public Sub SplitThem(addresses As String)
    Dim address As String
    Do While AnotherAddress(addresses, address)
        Parse (address)
    Loop
End Sub
 
==============================
 
Public Sub Parse(address As String)
    '   let's dump the quotes
    address = Replace(address, """", "")
     '   and the spaces following the commas
    address = Replace(address, ", ", ",")
'   now let's split the fields
    Dim flds() As String   '    0-based
    flds = Split(address, ",")
    
'   store them in the appropriate fields in the database
    '   in  the same table, or elsewhere
  Dim nv() As String  '   name, value
    rst.AddNew
       ' rst.Fields("TheName") = theName
        nv = Split(flds(0), ":")
        rst.Fields(nv(0)) = nv(1)
               
        nv = Split(flds(1), ":")
        rst.Fields(nv(0)) = nv(1)
               
        nv = Split(flds(2), ":")
        rst.Fields(nv(0)) = nv(1)
               
        nv = Split(flds(3), ":")
        rst.Fields(nv(0)) = nv(1)
           
           rst.Update
    
End Sub

So, how do I call this function? Should I calling it on 'Address' field? Or should I be calling on all the newly created fields?
I did debug the code to see if it's working properly, but it ends up in the error loop for some reason.
It's failing on inserting the split address into newly created fields.
nv = Split(flds(0), ":")
rst.Fields(nv(0)) = nv(1) 'it's failing here

I have created 4 new fields(street, city , zip, state) manually in the design view. And, I can see that nv(1) is properly getting the street name while debugging but failing to insert into nv(0). What am I doing wrong?

Any help would be great. Thank you.

 

[arnelgp]

You should inspect the value of NV(0). Does it hold the fieldnname in rs?

 

[gilli]

Thanks Arnelgp,
It shows a double quote in nv(0).

Like so:

nv(0)="

Each field and value are wrapped in double quotes.

fld(0) "street:43 xxx rd"

should I stripping those double quotes?

 

[pbaldy]

I'll get out of the way.

 

[gilli]

Oops, my bad.
nv(0) contains "street" and nv(1) contains street address.

I am not sure why it is still failing after this step. :banghead:

 

[arnelgp]

since you know that nv(0) is for field "Street", why not hardcode it in your code:

nv=split(flds(0),":")
rst.fields("Street").Value = nv(1)

 

[gilli]

Arnelgp,
so basically, I am not sure if the field 'Street' has been created correctly or not in the query design view.
There is no field called Street in the query, I have manually created one by clicking insert column in the query design view and gave a name.