Update Query for a specific character

[yippie_ky_yay]

Hello everyone,

I am trying to replace all instances of "(" with "[", and all instances of ")" with "]".

I have been looking up update queries, but have only been successful with updates to the entire field or if you know exactly where the brackets will be (using MID$ or Left$).

Can someone show me a quick example of this or point me in the right direction please?

Any help would be greatly appreciated!

-Sean

 

[Jon K]

The quickest way is to open the table and choose the menu Edit, Replace...

 

[antomack]

The following function will do the job. Since it will just replace one string at a time you will need to run it twice, one for "(" and once for ")".

Public Function ReplaceWithChar(str As String, strFind As String, strNew As String)

Dim intLen As Integer, i As Integer, intPos As Integer

str = Trim(str)
intLen = Len(str)
For i = 1 To intLen
If InStr(i, str, strFind) <> 0 Then
intPos = InStr(i, str, strFind)
str = left(str, intPos - 1) & strNew & Right(str, intLen - (intPos))
i = intPos
End If
Next i
ReplaceWithChar = Trim(str)

End Function

Example:
replacewithchar("ss(dddd(ffg)","(","[") = "ss[dddd[ffg)"

 

[yippie_ky_yay]

Thanks for the replies Jon and Antomack!

I was actually looking for a programatic solution such as Antomack's as I plan to use it in a report.

I actually did come up with a solution:

If TITLE <> "" And TITLE Like "*(*" Then
txtTempTitle = TITLE.Value
txtTempTitle = Replace(txtTempTitle, "(", "[", 1, -1, vbTextCompare)
txtTempTitle = Replace(txtTempTitle, ")", "]", 1, -1, vbTextCompare)
End If

'I'll assume that if there is an "(" then there's a ")"

I'll still study over Antomack's solution in case it's better - I'm still pretty new to Access.

BTW - if there's anything that I'm doing that I shouldn't, please let me know (ie, I feel a little nervous using <> "" - first line of my If statement)

Thanks again!

-Sean

 

[antomack]

The following version of the function is more robust in that you can replace any length of string with any string. The other version didn't work properly where the string to be replaced was longer than 1 and repeated side by side. Have been meaning to update it for ages but kept forgetting.

Public Function ReplaceWithChars(str As String, strFind As String, strNew As String)
  ' Function takes in 3 strings
  ' str - string which contains characters to be replaced
  ' strFind - string that you want to replace
  ' strNew - string you want instead of strFind
  ' It replaces all occurences of strFind within str with strNew
  ' NB. Leading or trailing spaces on the string str will be removed

  Dim intLen As Integer, i As Integer, intPos As Integer, intFindLen As Integer, intNewLen As Integer

  intFindLen = Len(strFind)
  intNewLen = Len(strNew)
  ' check if strFind is null string
  If intFindLen <> 0 Then   ' strFind not null string
    str = Trim(str)
    intLen = Len(str)
    For i = 1 To intLen
      If InStr(i, str, strFind) <> 0 Then
        intPos = InStr(i, str, strFind)
        str = left(str, intPos - 1) & strNew & Right(str, intLen - (intPos + intFindLen) + 1)
        i = intPos + intNewLen - 1
        ' if strFind and strNew are of different lengths then the string length needs to be updated
        intLen = Len(str)
      End If
    Next i
    ReplaceWithChar = Trim(str)
  Else                      ' strFind is a null string
    ReplaceWithChar = Trim(str)
  End If
  
End Function