"Smiley" <fi*********@googlemail.comwrote in message
news:f2*******************@news.demon.co.uk...
Hi,
Can someone tell me how to remove aspostophe (') from user input.
I don't want to give any error message. Just want to remove or change it
to "" or null, such input when it appear anywhere on a specific field.
e.g. if user input abcd's. I want the final data on the database be look
like abcds or abcd s
MSAccess 2000
Anyone any idea ?
Many thanks in advance.
There may well be a more elegant approach but this works for me.
*** CODE BEGINS ***
Sub libRemoveCharacter(strSearchChar As String, strField As String, strTable
As String)
'Author: Keith Wilby
'Date: 27 May 2005
'Purpose: remove characters from fields
Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String, intPosition
As Integer
Dim strSearchString As String, strNewString As String
Set db = CurrentDb
strSQL = "Select " & strField & " From " & strTable & " Where InStr(nz([" &
strField & "]),""" & strSearchChar & """)>0"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then GoTo ExitSub
With rs
.MoveFirst
Do Until .EOF
strSearchString = rs(strField)
intPosition = InStr(1, strSearchString, strSearchChar, 1) - 1 'Count
the characters before the offending character
strNewString = Left(strSearchString, intPosition) 'Add the
characters before the offending character
strNewString = strNewString _
& Right(strSearchString, Len(strSearchString) - (intPosition + 1))
'Add the characters after the offending character
.Edit
rs(strField) = strNewString
.Update
'Debug.Print strNewString
intRecordCount = intRecordCount + 1 'Increment the count
.MoveNext
Loop
End With
ExitSub:
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
*** CODE ENDS ***
Call the sub from your form's After Update event and pass the search
character, field name and table name to it (Chr(39) represents the
apostrophe but you can pass any character to it):
Call libRemoveCharacter(Chr(39), "MyField", "tblMyTable")
I've never used it from a form's update event but I imagine it'll work OK.
HTH - Keith.
www.keithwilby.com