Question Split Text In A Field After Each Coma

[Dhamdard]

Hello experts,

Table1 has one field "Objectives". Table2 has one field too "ObjectivesBroken". Value for "Objectives" field is 'primary education, health and sanitation, vocational training'.

I am trying to run below code to break the value of "objectives" field after each coma and append the result to "ObjectivesBroken" field in Table2. I had tried it before and worked so well. This time I can't figure out what error do I make that it gives me this message "Number of query values and destination fields are not the same".

Private Sub Command0_Click()
Dim MyArray As Variant
Dim rs As DAO.Recordset
Dim i As Integer
Dim strSQL As String
 
Set rs = CurrentDb.OpenRecordset("Table1")
With rs
    .MoveFirst
    Do While Not .EOF
        MyArray = Split(!Objectives, ",")
            For i = 0 To UBound(MyArray)
            strSQL = "Insert Into Table2 (ObjectivesBroken) Values(""" _
                   & """, """ & MyArray(i) & """);"
            CurrentDb.Execute strSQL
        Next i
        .MoveNext
    Loop
End With

End Sub

Much appreciate your help.

Hamdard

 

[plog]

Whenever you have a problem executing SQL from a script always spit out the SQL you are trying to execute so that you can verify its parsed correctly. So, I'd change this:

CurrentDb.Execute strSQL

To this:

MsgBox(strSQL)

You might want to limit your while loop to just one record so that you don't get a message box for every row in your table. After you do that you will be able to see exactly what is in strSQL and why it is breaking.

My guess is those quote marks. You essentially have 2 sets of quote marks--one to show where strings begin and end, and one set that need to show up in your SQL. You've used double quotes for both of these sets which make it really hard for a human to digest the entire string in the code. My advice is to change one set to single quotes (') and leave the other set double quotes ("). That will make it easier for a human to read.

 

[jdraw]

I agree with plog, but I do it a little differently.

I often use a Debug.print MySqlstring which will print the MySqlstring to the immediate window. You can check it there visually for "obvious" errors.

Also I would insert an error handler -- a location to go to if an error occurs.
Then I would use the options on the dbExecute statement

CurrentDb.Execute strSQL,dbfailonerror

There is an execllent free tool called MZTools that can do many things with vba, including inserting Error Handling code with one button click.

see http://www.mztools.com/v3/mztools3.aspx

 

[vbaInet]

All good points from jdraw and plog.

I don't also see why you're inserting the zero-length string or even why you are needing to inserting the derived values into your table.

Your main problem is you have defined one source and given it two destinations.

 

[Dhamdard]

Thanks, guys. Finally did it. It's always good to go for a break when things get crazy and restart after being refreshed.