Parsing a field to seperate fields 1

[webcats]

Hi, I'm trying to parse a field that has a delimiter and different numbers of words into seperate fields. The input file had two different delimiters, "," and "|", so I imported with the comma delimeter, but now I'm trying to split out the fields between the |'s.

I found some code on a site to parse out the delimiters, but can't seem to get the field name to update to the correct number.

Becuase there may be 1 to 100 different pieces to parse, I've created the table with all the fields:

s1
r1
s2
r2
s3
r3

etc. up to 50. The field has a service type then a rate type. Here is a piece of the code I have:

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
strAString = rs1!info

Do While rs1.EOF = False

With rs2
.AddNew

rs2!COMPANY = rs1!COMPANY
rs2!AGNT = rs1!AGNT
rs2!PKG = rs1!PKG
rs2!Description = rs1!Description
End With


'Now call the other function to retrieve each one in turn

For I = 1 To intCnt

rsSI = "rs2!s" & I
rsRI = "rs2!r" & I

stWord = GetCSWord(strAString, I)

' Debug.Print GetCSWord(strAString, I)


If Left(stWord, 1) = "$" Then
With rs2
rsRI = stWord
End With
Else
rsSI = stWord
End If

rs2.Update

Next
rs1.MoveNext
strAString = rs1!info

Loop

rs1.Close

rs2.Close
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The problem I'm have is with these lines:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
rsSI = "rs2!s" & I
rsRI = "rs2!r" & I
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
and
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
If Left(stWord, 1) = "$" Then
With rs2
rsRI = stWord
End With
Else
rsSI = stWord
End If
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

It won't let me increment the field name by one to pick the next parsed word. Do I have to code in all 100 fields?

Any help would be appreciated. Thanks.


"One Database to rule them all, One Database to find them,
One Database to bring them all and in the darkness bind them."

 

[RoyVidar]

If the challenge here is how to refer to fields dynamicly, then try:

[tt]rsSI = rs2("s" & I)
rsRI = rs2("r" & I) [/tt]

Roy-Vidar

 

[webcats]

HI Roy, yes, that's what I was trying to do. Thanks. But I now get a compile error: Type-declaration character does not match declared data type.

I is an Iteger, so I created a string "stNum" and put:
stNum = I. Well I got the same error so I tried:
"stNum = CStr(I)"

I changed the other code to read:
" With rs2
rs2!("r" & stNum) = stWord"

But I'm still getting the same compile error.
Any ideas? Thanks!

"One Database to rule them all, One Database to find them,
One Database to bring them all and in the darkness bind them."

 

[RoyVidar]

For the first set of code in the last reply, I don't understand what you mean, for the second, drop the bang (!) as in my sample. It's OK to use an integer.

Roy-Vidar

 

[webcats]

Thanks Roy! It works great! (I missed the bang part)

 

[PHV]

For your parsing issue, you may consider the Split function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244