Follow Up Post to Parseing Text

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

Steven M. Britton

The below code doesn't currently work, looking for any
ideas - I have also included at the bottom the original
string from this newgroup. I have made a slight change
from my original question. Originally I wanted to take
the data out of a link txt file and split the Address
field so I could then add it to a perment table. Now all
I need to do is just split the text with in its current
table. Thanks again.



Option Compare Database

Private Sub btnUpdateDazzleRecords_Click()
On Error GoTo Err_btnUpdateDazzleRecords_Click

Dim strAddrParts(1) As String

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

rs.MoveFirst
Do While Not rs.EOF
strAddrParts = Split(rs!Address, ",")
' Read each element in the array
rs.AddNew
rs!ContactName = strAddrParts(0)
rs!CustReference = strAddrParts(1)
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Exit_btnUpdateDazzleRecords_Click:
Exit Sub

Err_btnUpdateDazzleRecords_Click:
MsgBox Err.Description
Resume Exit_btnUpdateDazzleRecords_Click

End Sub


-----------------------------------------------------------
Original Post and Answer-----------------------------------

. Reply (E-mail) Forward (E-mail)

Subject: Re: MVP Needed - Parse a string of text from a
linked .txt file
From: "Cheryl Fischer" <[email protected]>
Sent: 4/27/2004 5:02:10 PM




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


message
 
Method 1

Dim strAddr As Strin
Dim strCName As Strin
Dim strCRef As Strin
Dim intPos As Intege

rs.MoveFirs
Do While Not rs.EO
'--- avoid null problems
strAddr = rs!Address & "

'--- parse names ...
intPos = InStr(1,strAddr, ","
If intPos > 0 The
strCName = Left$(strAddr, intPos - 1
strCRef = Mid$(strAddr, intPos + 1
Els
strCName = strAdd
strCRef = "
End I

rs.AddNe
rs!ContactName = strCNam
rs!CustReference = strCRe
rs.Updat
rs.MoveNex
Loo


Method 2

Dim strAddr As Strin
Dim varAddrParts as Varian
Dim strCName As Strin
Dim strCRef As Strin

rs.MoveFirs
Do While Not rs.EO
'--- avoid null problems
strAddr = rs!Address &"

varAddrParts = Split(strAddr, ","
If IsArray(varAddrParts) The
If UBound(varAddrParts) = 1 The
strCName = varAddrParts(0
strCRef = varAddrParts(1
Els
strCName = strAdd
strCRef = "
End I
Els
strCName = "
strCRef = "
End I

rs.AddNe
rs!ContactName = strCNam
rs!CustReference = strCRe
rs.Updat
rs.MoveNex
Loo
 
Back
Top