Parsing one field into multiple records

Kenneth Marks
Kenneth Marks used Ask the Experts™
on
Hi,

This is an addition to a previously asked question:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26301876.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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
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], "") & "');"
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], "") & "');"


Top Expert 2016

Commented:
the wrapper chr(34) can handle the single quotes, no need to replace them.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Kenneth MarksProgramming Manager

Author

Commented:
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
Top Expert 2016

Commented:



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

Kenneth MarksProgramming Manager

Author

Commented:
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
Top Expert 2016

Commented:
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)) & "');"
 
Top Expert 2016
Commented:
here is the whole code - revised 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) & ", '" & 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

Kenneth MarksProgramming Manager

Author

Commented:
Capricorn1,

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


Ken

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial