jk

  • Thread starter Thread starter hamid _rahmatrolahe
  • Start date Start date
H

hamid _rahmatrolahe

Hi,

I was wanting to know the easiest way to substitute a
field of data in ms access using sql for all the rows in a
column.

ie if l have a field that is say
"micro+soft+access"

I need to be able to change it to
"micro soft access"
using an SQL DML update statement do to the size of the
database.

Kind Regards
 
hamid _rahmatrolahe said:
Hi,

I was wanting to know the easiest way to substitute a
field of data in ms access using sql for all the rows in a
column.

ie if l have a field that is say
"micro+soft+access"

I need to be able to change it to
"micro soft access"
using an SQL DML update statement do to the size of the
database.
Click to expand...

In version 2000 and later, use the Replace() function. In earlier versions,
you can use this custom function:

Function ChangeString(strIn As String, strToFind As String, strReplace As
String, _
Optional intCount As Variant) As String
On Error Resume Next

Dim intPlace As Integer
Dim intCounter As Integer
Dim intStart As Integer

If IsMissing(intCount) Then intCount = 32767
intStart = 1

For intCounter = 1 To intCount
intPlace = InStr(intStart, strIn, strToFind)
If intPlace > 0 Then
strIn = Left(strIn, intPlace - 1) & strReplace & Mid(strIn,
intPlace + Len(strToFind))
intStart = intPlace + Len(strReplace) + 1
Else
Exit For
End If
Next

ChangeString = strIn

End Function

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top