asked on

Parsing one field into multiple records

Hi,

This is an addition to a previously asked question:
https://www.experts-exchange.com/questions/26301876/Parsing-one-field-into-multiple-records-while-also-keeping-the-records-with-nothing-in-the-field.html

Everything works great except for some of the customer names has an Apostrophes within the name. I am getting this error when writing to the new table.

Run-time error 3075
Syntax error (missing operator) in query expression "Today's Shopper",'08032');'.

When I remove the Apostrophe everything works fine.

Public Function SplitUsers()
    
    Dim RS As DAO.Recordset
    Dim Accts() As String
    Dim I As Integer
    Dim SQL As String
    
    Set RS = CurrentDb().OpenRecordset("ROI2")
    
    RS.MoveFirst
    Do Until RS.EOF = True
    
    
        If RS.EOF = True Then Exit Do '<-- may cause you to miss the last record.
        
        
        If InStr(1, Nz(RS![Zips Served], ""), ",") < 1 Then
            SQL = "INSERT INTO TblTempProcessing " & _
            "([Temp_ID], [Temp_Customer_Number], [Temp_Customer_Name], [Temp_Zip]) " & _
            "VALUES ('" & RS![ID] & "', '" & RS![Customer Number] & "', '" & _
                    RS![Customer Name] & "', '" & Nz(RS![Zips Served], "") & "');"
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQL, False
            DoCmd.SetWarnings True
        Else
        
            'Split by comma into a string array
            Accts = Split(RS![Zips Served], ",")
        
            'Go thru array and populate final table
            For I = 0 To UBound(Accts)
        
                SQL = "INSERT INTO TblTempProcessing " & _
                "([Temp_ID], [Temp_Customer_Number], [Temp_Customer_Name], [Temp_Zip]) " & _
                "VALUES ('" & RS![ID] & "', '" & RS![Customer Number] & "', '" & _
                        RS![Customer Name] & "', '" & Trim(Accts(I)) & "');"
                DoCmd.SetWarnings False
                DoCmd.RunSQL SQL, False
                DoCmd.SetWarnings True
            
            Next I
        End If
        'Check next record
        RS.MoveNext
    Loop
    
    Set RS = Nothing
 
End Function

Open in new window

Rey Obrero (Capricorn1)

use this format

SQL = "INSERT INTO TblTempProcessing " & _
"([Temp_ID], [Temp_Customer_Number], [Temp_Customer_Name], [Temp_Zip]) " & _
"VALUES ('" & RS![ID] & "', '" & RS![Customer Number] & "', " & _
chr(34) & RS![Customer Name] & chr(34) & ", '" & Nz(RS![Zips Served], "") & "');"

Paul_Harris_Fusion

The problem is with the apostrophe in the data which is confusing the parser.

You need to escape them by replacing with two apostrophes - use the Replace function for this

e.g.

SQL = "INSERT INTO TblTempProcessing " & _
"([Temp_ID], [Temp_Customer_Number], [Temp_Customer_Name], [Temp_Zip]) " & _
"VALUES ('" & RS![ID] & "', '" & RS![Customer Number] & "', '" & _
Replace ( RS![Customer Name] , "'", "''") & "', '" & Nz(RS![Zips Served], "") & "');"

Rey Obrero (Capricorn1)

the wrapper chr(34) can handle the single quotes, no need to replace them.

Kenneth Marks

ASKER

Capricorn1,

Thanks for the quick reply.

I added the changes to both areas and now the zip codes are not parsing out correctly. The records are being made in the new table but the zips are repeating in the original way. Please see attachments.

Here is the code:

Public Function SplitUsers()
    
    Dim RS As DAO.Recordset
    Dim Accts() As String
    Dim I As Integer
    Dim SQL As String
    
    Set RS = CurrentDb().OpenRecordset("ROI2")
    
    RS.MoveFirst
    Do Until RS.EOF = True
    
    
        If RS.EOF = True Then Exit Do '<-- may cause you to miss the last record.
        
        
        If InStr(1, Nz(RS![Zips Served], ""), ",") < 1 Then
            SQL = "INSERT INTO TblTempProcessing " & _
            "([Temp_ID], [Temp_Customer_Number], [Temp_Customer_Name], [Temp_Zip]) " & _
            "VALUES ('" & RS![ID] & "', '" & RS![Customer Number] & "', " & _
                   Chr(34) & RS![Customer Name] & Chr(34) & ", '" & Nz(RS![Zips Served], "") & "');"
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQL, False
            DoCmd.SetWarnings True
        Else
        
            'Split by comma into a string array
            Accts = Split(RS![Zips Served], ",")
        
            'Go thru array and populate final table
            For I = 0 To UBound(Accts)
        
                SQL = "INSERT INTO TblTempProcessing " & _
                "([Temp_ID], [Temp_Customer_Number], [Temp_Customer_Name], [Temp_Zip]) " & _
                "VALUES ('" & RS![ID] & "', '" & RS![Customer Number] & "', " & _
                        Chr(34) & RS![Customer Name] & Chr(34) & ", '" & Nz(RS![Zips Served], "") & "');"
                DoCmd.SetWarnings False
                DoCmd.RunSQL SQL, False
                DoCmd.SetWarnings True
            
            Next I
        End If
        'Check next record
        RS.MoveNext
    Loop
    
    Set RS = Nothing
 
End Function

Open in new window

Incorrect.JPG
Correct.JPG

Rey Obrero (Capricorn1)

the wrapper chr(34) has nothing to do with the parsing of the records..

Kenneth Marks

ASKER

capricorn1,

I will go over this again, but before I make the wrapper changes it parses correctly, I add them and now it does not. I will start over and test again. Thanks for your help.

Ken

Rey Obrero (Capricorn1)

you changed the second SQL with the first one, use this on the second SQL

SQL = "INSERT INTO TblTempProcessing " & _
"([Temp_ID], [Temp_Customer_Number], [Temp_Customer_Name], [Temp_Zip]) " & _
"VALUES ('" & RS![ID] & "', '" & RS![Customer Number] & "', " & _
chr(34) & RS![Customer Name] & chr(34) & ", '" & Trim(Accts(I)) & "');"

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kenneth Marks

ASKER

Capricorn1,

Your right, I had the code messed up and now it works great! Thanks very much.

Ken