db.Execute, Mid, Replace, and error 5

[weightinwildcat]

I have been working on a form that allows users to enter information on parts prior to having work done on a CNC machine. The information will be added to the Message field of a new record, the field is Long Text.

In the course of adding the information we need to get rid of some special characters, since the new record will be added using a query that is saved in a String variable and run with the db.Execute command.

The code for getting rid of special characters is presently as follows:

charCount = Len(newText)

For i = 1 To charCount
If Asc(Mid(newText, i, 1)) = 39 Then
newTextChar = Mid(newText, i, 1)
newText = Replace(newText, newTextChar, "''", i, 1)
If Asc(Mid(newText, i, 1)) = 34 Then
newTextChar = Mid(newText, i, 1)
newText = Replace(newText, newTextChar, """", i, 1)
End If
End If

newTextChar = ""
Next

I am trying to make things more modular in case we ever need to get rid of any other special characters. However, I have run into error number 5 when I try different configurations for this code.

In one case I did this:

For i = 1 To charCount
If Asc(Mid(newText, i, 1)) = 39 Then
newTextChar = Mid(newText, i, 1)
newText = Replace(newText, newTextChar, "''", i, 1)
End If

newTextChar = ""
Next

For j = 1 To charCount
If Asc(Mid(newText, j, 1)) = 34 Then
newTextChar = Mid(newText, j, 1)
newText = Replace(newText, newTextChar, """", j, 1)
End If

newTextChar = ""
Next

In another case I did this:

If Asc(Mid(newText, i, 1)) = 39 Then
newTextChar = Mid(newText, i, 1)
newText = Replace(newText, newTextChar, "''", i, 1)
ElseIf Asc(Mid(newText, i, 1)) = 34 Then
newTextChar = Mid(newText, i, 1)
newText = Replace(newText, newTextChar, """", i, 1)
End If

newTextChar = ""

I also tried this:

For i = 1 To charCount
If Asc(Mid(newText, i, 1)) = 39 Then
newTextChar = Mid(newText, i, 1)
newText = Replace(newText, newTextChar, "''", i, 1)
ElseIf Asc(Mid(newText, i, 1)) < 32 Then
newTextChar = Mid(newText, i, 1)
newText = Replace(newText, newTextChar, " ", i, 1)
ElseIf Asc(Mid(newText, i, 1)) > 126 And Asc(Mid(newText, i, 1)) < 192 Then
newTextChar = Mid(newText, i, 1)
newText = Replace(newText, newTextChar, " ", i, 1)
End If

newTextChar = ""
Next

So why does everything but the first method give me an error 5?

 

[dhookom]

Please use TGML tags to format your code so it is easier to read:

charCount = Len(newText)
For i = 1 To charCount
    If Asc(Mid(newText, i, 1)) = 39 Then
        newTextChar = Mid(newText, i, 1)
        newText = Replace(newText, newTextChar, "''", i, 1)
        If Asc(Mid(newText, i, 1)) = 34 Then
            newTextChar = Mid(newText, i, 1)
            newText = Replace(newText, newTextChar, """", i, 1)
        End If
    End If
    newTextChar = ""
Next

I would move all of this to a function that you can call and more easily test.
Do you realize in the first section you will only replace CHR(34) if there is a CHR(39).
Have you tried setting a breakpoint to step through your code?

I think you can do something like:

Public Function DoubleUpQuotes(strText as String) as String
    Dim strOut as String
    strOut = Replace(strText ,chr(34),chr(34) & chr(34))
    strOut = Replace(strText ,chr(39),chr(39) & chr(39))
    DoubleUpQuotes = strOut
End Function

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[combo]

Replace is not a good function here, it overwrites file ([tt]replace("abcde","b","xx",2,1)[/tt] returns [tt]xxcde[/tt]).
I don't know it this exactly matches what you need, but you can see the idea:

Dim TextArray() As String, TextLength As Long
TextLength = Len(newText)
If TextLength > 0 Then
    ReDim TextArray(1 To TextLength)
    ' split text
    For i = 1 To TextLength
        TextArray(i) = Mid(newText, i, 1)
    Next i
    ' replace text
    For i = 1 To TextLength
        If TextArray(i) = "'" Then TextArray(i) = "''": Exit For
        If TextArray(i) = """" Then TextArray(i) = """""": Exit For
        ' etc
    Next i
    ' join text
    newText = ""
    For i = 1 To TextLength
        newText = newText & TextArray(i)
    Next i
End If

combo

 

[dhookom]

The Replace() usage that I suggested
replace("abcde","b","xx") returns axxcde
I think this is what was specified but I didn't attempt to review all of the code because of time and lack of code formatting.

Ideally the function should allow the arguments for the characters to substitute.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[combo]

Increasing number of characters changes current position in loop and length of string, this requires additional adjustment of the index in loop, some charcters are replaced by single space (last loop in OP's initial post).

combo

 

[dhookom]

The code I suggested doesn't have any looping or index. Maybe weightinwildcat will weight in and let us know if a simple replace works.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016