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

From: Cheryl Fischer (cherylfischer_at_NOSPAMmsn.com)
Date: 04/28/04 

Date: Tue, 27 Apr 2004 19:02:10 -0500

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, 

--
Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX
"Steven M Britton" <sbritton@nomail.arc-sd.com> wrote in message
news:50fb01c42caf$2c355c50$a601280a@phx.gbl...
> 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
>

Relevant Pages

  • Re: Load a string of CSV from memory into a dataset
    ... If you copied the data to a file with the extension .CSV and then opened it ... Dim strFileName As String ... until AFTER then next Quotation Mark that is followed by a Comma. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: returning values from multiselect list as string
    ... Dim p as long ... > string in reverse. ... Jonathan brilliantly decided to build the string in reverse because ... > but with a comma as a separator. ...
    (microsoft.public.word.vba.beginners)
  • RE: Trouble with concatenate function
    ... try creating a new query. ... > values from the Many table of a 1:M relationship in a comma separated format. ... > Dim rs As Recordset ... > Dim strCriteria As String, ...
    (microsoft.public.access.queries)
  • Re: Select Within String -> Propercase
    ... Dim sStrn As String ' the string ... Dim sLeft As String ' the left part ... Dim lComm As Long ' the position of the comma ...
    (microsoft.public.word.vba.userforms)
  • Re: CDBL Function truncates decimals
    ... Public Function ConvertNumberforSQL (ByVal sValue As String, ... vlDefaultNumber As Double= 0) as Double ... Since the Spanish formatting has a comma, the SQL statement seems to have ...
    (microsoft.public.vb.general.discussion)