replacing specific elements in strings 1

[pyttviper]

It has been a while since i have used jet sql (mostly use pl/sql). I need to replace specific chars in a string but can't find a method available in jet sql.

Example

"TASK: "IB732B" FROM QUALITY"

I want to replace the &QUOT with a space.
I can do this in pl/sql but the solution eludes me in access 97 jet sql. also, i am doing this in a query.

thanks
Dan0

Dan0

 

[Norris68]

I'm glad you mentioned you were using Access 97. In Access 2000 and above you could use the Replace function, but it didn't exist back in 97.

Create a module and paste the following function:

Public Function SandR(InString As String, SearchString As String, ReplaceString As String) As String
'Search & Replace Function
Dim A As Long
Dim S As String
Dim T As String

S = InString
T = ""
'While Still Some To Do
While Len(S) > 0
'Look For SearchString In What Is Left (S)
A = InStr(1, S, SearchString)
'If Found
If A > 0 Then
'Copy The Bit Before That Along With ReplaceString
T = T & Left$(S, A - 1) & ReplaceString
'Chop Off The Bit We Just Did
S = Mid$(S, A + Len(SearchString))
Else
'Not Found - Chuck The Rest Of The String Into T And Truncate S
T = T & S
S = ""
End If
Wend
SandR = T
End Function

Within Access you can then use this public function within queries. e.g.
SELECT SandR(MyField,'"',' ') AS MyField2 FROM ...

Ordinarily within Access VBA you would have to specify a string using double quote marks. However, within a query you can use single quotes. To use double-quotes you would have had to delimit each double within the string with another double i.e. SandR(MyField,""""," &quot

 

[BiggerBrother]

why not try replace(mystring,""""," &quot in your select statement as it is a standard VB statement.

 

[Norris68]

I'm glad you mentioned you were using Access 97. In Access 2000 and above you could use the Replace function, but it didn't exist back in 97.