MVP Needed - Parse a string of text from a linked .txt file

  • Thread starter Thread starter Steven M Britton
  • Start date Start date
S

Steven M Britton

I use a system to send packages US Mail, the reporting
functions aren't the best. So it exports a file that is
tab delimited, so with that I am able to link to it and
break the fields out that I need. (ie: Address, ZipCode,
Postage, Tracking_Number).

Now what I need to do is break down the address field
further. It currently exports the address field into one
column and breaks it up by commas. So I have Name,
Company, Address1, City, State, Zip - How can I parse this
data into Access via a Query or VB Code.

I took a semester in Java for kicks and was able to learn
about a function Java as indexof. - I would assume that
Microsoft has something similar built into there
programming language. (We can't let SUN show them up.)
So are there any MVP's that understand my question and can
offer a solution?
-Steve
 
Steven,

You will need to use some VBA for this, I believe, with the Split() function
delimiting the string on the comma. Perhaps something like the following
(untested) code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strAddrParts() As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblAddress", dbOpenDynaset)

rs.MoveFirst
Do While Not rs.EOF
ReDim strAddrParts(0)
strAddrParts = Split(rs!Addr, ",")
' Read each element in the array
rs.AddNew
rs!AddresseeName= strAddrParts(0)
rs!Company = strAddrParts(1)
<continue with code to add address fields>
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

hth,
 
Back
Top