Removing "????" and "***" from text fields 1

[SmirnoffBlu]

Im trying to use SQL to delete the characters


"????" and "****" from text fields.


Not having much luck....


Any ideas?

 

[scking]

Very simply combine SQL with a function. In the query builder add this to a blank column, changing the obvious generic parameter to the field name for your query.

NewText:GetCleanText([FieldNameToBeCleaned])

And the NewText column will then display the cleaned text.

Public Function GetCleanText(MyText) As String

Dim intLen As Integer
Dim intCount As Integer
Dim strMyText As String
Dim strChar As String

intLen = Len(MyText)
For intCount = 1 To intLen
strChar = Mid$(MyText, intCount, 1)
If strChar <> &quot;?&quot; And strChar <> &quot;*&quot; Then
strMyText = strMyText & Mid$(MyText, intCount, 1)
End If
Next intCount
GetCleanText = strMyText

End Function
----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[corvetta]

What aboout the function InStr(start,string1,string2), where start= position from you start the search, string1= string to examine, string2=goal string (in your case the &quot;?&quot; or the &quot;*&quot

The function returne the first position of string2 starts; with a cicle, you can substitute the char you need.


I hope this help is useful for you.
Bruno

 

[BlackKnight]

Hi,
Try this...

Private Sub Command6_Click()
Dim conn As Connection
Dim rs As New Recordset

Set conn = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.LockType = adLockPessimistic
rs.Open &quot;SELECT * FROM City&quot;, conn
With rs
If rs.EOF And .BOF Then
MsgBox &quot;No records found&quot;
Else
Do While Not .EOF
!City = Replace(!City, &quot;*&quot;, &quot;&quot
!City = Replace(!City, &quot;?&quot;, &quot;&quot
.Update
.MoveNext
Loop
.Close
End If
End With
Set rs = Nothing: conn.Close: Set conn = Nothing
End Sub

Have a good one!
BK

 

[LittleSmudge]

Thwack


Multi Post Alert !

thread701-326039