Parse table with field of varying lengths

[Delon V. Ottley]

Looping is getting me loopy. I think all of you are trying on my patience

You need to loop through it somehow. Since you are calling the function from
a query, you should be able to just make a select query that selects F1 & F2
from tblA. The query will run each record through the function one time.
That assumes that the un-split F1 is already paired with a correct F2 in
tblA, as it looks like in your example:

tblA:
F1
12345678900001;12345678900002;12345678900003
23456789100001
34567891200001;3456789120002

F2
aaaaa
bbbbb
ccccc

Here's a bit of air code to try in a query. Just make sure tblB exists and
is empty before running.

SELECT F1, F2, spl([F1],";",[F2]) from tblA

Try pasting that into the SQL of a query and running it. The third field in
the query will cause every record in tblA to be run through the function.

F1 & F2 are both text.

Good news is that the table was created, the bad news is that every record
contained the same F2.

Do I need to create another loop for each different F2 value? Loop through
F2 value then loop through each F1 splitting at the se,i-colon?

Oops. I had the quotes wrong (so much for my "air code"):

strSQL = "INSERT into tblB (F1, F2) SELECT '" & strCurrent & "','" & F2Val
& "'"

There are some single & double quotes in there. This assumes both F1 & F2
are text fields. If F1 is actually a numeric field:

strSQL = "INSERT into tblB (F1, F2) SELECT " & Val(strCurrent) & ", '" &
F2Val & "'"

:

Public Function spl1(V As Variant, D As Variant, F2Val As String) As Variant
Dim varSplit As Variant
Dim strCurrent As String
Dim strSQL As String
varSplit = Split(V, D, -1, 0)
Dim intMembers As Integer
intMembers = UBound(varSplit) 'identify highest array member
Dim intCurrent As Integer
For intCurrent = 0 To intMembers
strCurrent = varSplit(intCurrent)
strSQL = "INSERT into tblB (F1, F2) SELECT strCurrent, F2Val"
CurrentDb.Execute strSQL, dbFailOnError
Next intCurrent
End Function

I put the SQL line correction with the quotes from your previous post. It
gave Too few parameters. Expected 1.

My F2Val is a string that I put in quotes when I call the function in my
query , right?. Like this:

spl1([field_name],";","104140") where [field_name]=F1 and 104140 is a value
in F2.

sp


:

Please post the function as it exists now. Also, I think I may have omitted
some quotes in the strSQL. Since strCurrent is a string, that line may need
to be like this:

strSQL = "INSERT into tblB (F1, F2) SELECT '" & strCurrent & "', F2Val"

:

Brian,
I placed the function in my query, however, I get a 'Runtime error 3061 Too
few parameters. Expected 2.' that comes up and highlights the
CurrentDB.Execute line for debug. Don't know what is missing. Thanks.

sp

:

The problem is that your function returns only the last spl, without doing
anything with all the prior ones.

For intCurrent = 0 To intMembers 'first one is assigned 0
spl = varSplit(intCurrent)
'LOOK AT EXAMPLE BELOW FOR WHAT NEEDS TO GO HERE
Next intCurrent

By the time it gets past this point, it is on the last array memeber, which
ends up being the value returned by the function.

Include the value of the particular F2Val as a third argument, then do the
INSERT statements within the function. If you are calling it from a query
(probably if this is a one-time action), leave this as a function but make
the query a simple select query (since the spl returned will be only the last
one and therefore relativelyl meaningless), leaving the INSERT action up to
the function. If you are calling this from code where you are looping through
a recordset based on tblA (probably if you need to repeat this process
later), make it a procedure, as below, instead of a function.

Public Sub spl(V As Variant, D As Variant, F2Val As String) As Variant
Dim varSplit As Variant
Dim strCurrent as String
Dim strSQL As String
varSplit = Split(V, D, -1, 0)
Dim intMembers As Integer
intMembers = UBound(varSplit) 'identify highest array member
Dim intCurrent As Integer
For intCurrent = 0 To intMembers
strCurrent = varSplit(intCurrent)
strSQL = "INSERT into tblB (F1, F2) SELECT strCurrent, F2Val"
CurrentDb.Execute strSQL, dbFailOnError
Next intCurrent
End Sub

Then, run this once for each record in tblA by looping through its records.

Note that I would not use "Split" as a field name in tblB - try to avoid
using reserved words as field or variable names - I have replaced it with
just F1 as the field name.

:

Brian,
I put your code into a public function but when it runs it skips a lot of
data or only shows the last string. Any ideas?

Public Function spl(V As Variant, D As Variant) As Variant
Dim varSplit As Variant
varSplit = Split(V, D, -1, 0) 'create an array based on semicolon-delimited
field
Dim intMembers As Integer
intMembers = UBound(varSplit) 'identify highest array member
Dim intCurrent As Integer
For intCurrent = 0 To intMembers 'first one is assigned 0
spl = varSplit(intCurrent)
Next intCurrent
End Function

:

Here is a sample split/loop code. It does not update your tables (see my
question below for info needed for that):

Dim varSplit As Variant
varSplit = Split(F1, ";", -1, 0) 'create an array based on
semicolon-delimited field
Dim intMembers As Integer
intMembers = UBound(varSplit) 'identify highest array member
Dim intCurrent As Integer
For intCurrent = 0 To intMembers 'first one is assigned 0
MsgBox varSplit(intCurrent)
Next

On your tblB, will the data be numbers or strings? Are you indicating that
you want to match on the first five characters of the split data, on the
numeric value of the first five characters, or any number of characters at
the beginning of the string? A little more detail will help us give a more
specific example in response.

:

Hello not very adept with VBA programming. Here is what I have tblA:

F1
12345678900001;12345678900002;12345678900003
23456789100001
34567891200001;3456789120002

Field F1 can have from 1 to n strings

F2
12345
23456
34567

Field F2 will only have 1 string

What I want in tblB:
F1 F2
12345678900001 12345
12345678900002 12345
12345678900003 12345
23456789100001 23456
34567891200001 34567
34567891200002 34567

I know there is a split function, I do not know how to incorporate this with
loops into vba. Any help?