Splting multiple values from one field into a new table

[Okenbarrel]

Greetings all,
I have a table that shows a contract number in one field and it has a field called competing contracts. The competing contracts field can be null or it can have one or many other contract numbers in it. If it has many contract numbers then they are seperated by semicolons. if there is only on the semicolon is not there. I am attempting to create a new table with the first contract number and then the next field would hold one competing contract number. So there would be multiple instances of the original contract number and only one instance of each competing contract number. I am coding only in VB. Below is the code I have used thusfar. The source file is from another company so I have no control over the column as it is sent to me.

Public Sub Competing()
Dim strMaster As String
Dim strCompetitors As String
Dim varCompetitors As Variant
Dim i As Integer
With CurrentDb.OpenRecordset("File Import")
If Not .BOF Then
.MoveFirst
Do While Not .EOF 'For each record in File Import...
strMaster = ![Master Contract Number]
If Len(Nz(![Related Contracts], "")) <> 0 Then 'If there are related contracts...
varCompetitors = Split(![Related Contracts], ";") 'Split the related contracts into an array
For i = 0 To UBound(varCompetitors) 'Parse through the array adding each related contract to the table
CurrentDb.Execute "INSERT INTO Competing_Contract_List ( [System Contract], [Competing Contract] ) " & _
"SELECT " & strMaster & ", " & varCompetitors(i) & ";"
Next i
End If
.MoveNext
Loop
End If
End With
End Sub


This code was suggested from another forum and seems much better than what I was trying originally. However, I am getting an error with this code as well.

Run Time Error '3075'
Syntax Error (missing operator) in query expression 'PP-OR-017A'


The error relates to the following line in the code.

CurrentDb.Execute "INSERT INTO Competing_Contract_List ( [System Contract], [Competing Contract] ) " & _
"SELECT " & strMaster & " , " & varCompetitors(i) & " ;"


If anyone can help me figure out what might be wrong in this code I would apprieciate it.

Tim

 

[RuralGuy]

How about:
For i = 0 To UBound(varCompetitors)-1

Edit: I'm probably wrong here! rg

Could it be flakey data?
A Debug.Print "Array Data [" & varCompetitors(i) & "] for index = [" & i & "]"

...might reveal the problem. I think a Null field will cause the SQL to barf.

 

[Okenbarrel]

Got it working. Thanks.

 

[RuralGuy]

Cool. Care to share what the problem was with us?

 

[RuralGuy]

No need to respond. I saw the answer in the UA Forum.

"SELECT '" & strMaster & "' , '" & Trim(varCompetitors(i)) & "' ;"

 

[Okenbarrel]

Yup. Missing quotes.