When I run the qry I get 3 fields displayed (Expr1, _0,_2). Expr1 is always
blank. As a result of running the query tblB is populated and ends at the
same record "most" of the time. Twice it added 2 records past the normal end.
When the query runs from design view I get 188 records returned in tblB (the
first 48 F2's repeated). When I open it from the objects window it gives me
284 records. This is where it will show the 1st 50 records then repeat the
1st 48 and then repeat the 48 again. (note that 1 F2 can have multiple F1's)
The data comes from the same place and I do not see any differences than the
one it stops on.
Still stumped.
:
Well, we know that the problem is not in the function, because this alone
SELECT tblA.[_0], tblA.[_2] FROM tblA;
stops at some point.
Can you identify what might be wrong with the record following the last
successful one? Does it always stop at the same point? If so, run it, then
flush tblB, then run it again and see if it gets to the same record. I'm a
little mystified as to why a query would return results to a certain point
unless there is something different about the rest of the records.
:
Please see my responses (many thanks):
:
I would test it first by replacing the function call with just F2 and see if
it copies all the existing records from tblA to tblB as-is (unsplit data).
This will tell you if it is the split function or something else that is
blocking the records from being copied.
Still cuts off the records.
Another test is to add an autonumber field to tblA, pass it to the function
as another argument, then include that field in the INSERT statement (but to
a simple Long field - it obviously cannot be inserted to another AutoNumber
field). Have your select query sort by this field, and after running the
process, look at tblB to identify whether it actually stops at some point or
if it misses records intermittently.
It only shows the records in order without skipping and then stops.
Are there tblA records that have null F2, perhaps?
tblA does not contain null values.
If you are calling the function from a query, paste the exact SQL of the
query you are using here, leaving actual field names intact. If you are
looping through a recordset, paste the VBA code that loops through the
recordset. Perhaps we can spot something that might be filtering out records.
Calling from a query:
SELECT tblA.[_0], tblA.[_2], spl1([_0],";",[_2]) AS Expr1
FROM tblA;
Also, if you search the forums on dbFailOnError. There are ways to check the
result of the Execute to log or display results based on whether the SQL
statement itself is successful or fails.
:
Sorry, the table does contain single string records.
It just stops looping at some point. The table only contains a fraction of
the data.
:
tblB does not contain all the records that it should. I cannot find a reason
at this point. It seems that all the records with only a single string in F1
(no semi-colon at the end) do not show on the table. However, there are also
records that do not show that contain multiple strings in F1.
If it loops through each record, why would it not show some of them?
:
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?
