INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

db.Execute, Mid, Replace, and error 5

db.Execute, Mid, Replace, and error 5

(OP)
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?

RE: db.Execute, Mid, Replace, and error 5

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

CODE --> vba

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:

CODE --> vba

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

RE: db.Execute, Mid, Replace, and error 5

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

CODE -->

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

RE: db.Execute, Mid, Replace, and error 5

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

RE: db.Execute, Mid, Replace, and error 5

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

RE: db.Execute, Mid, Replace, and error 5

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close