Hi John,
First, make a backup of your database, just in case anything goes wrong.
This is always a good idea when you are running any type of action query for
the first time.
Create a new module. Add the following function to this module:
Option Compare Database
Option Explicit
Public Function ReplaceMe(varData As Variant) As Variant
Dim strReturn As String
Dim intLength As Integer
Dim intCharCode As Integer
Dim i As Integer
If Not IsNull(varData) Then
'Do a character-by-character search and replace
intLength = Len(varData)
For i = 1 To intLength
intCharCode = Asc(Mid(varData, i, 1))
Select Case intCharCode
Case 45, 48 To 57, 65 To 90, 97 To 122 'Hyphen, 0-9, A-Z,
a-z
strReturn = strReturn & Mid(varData, i, 1)
Case Else
End Select
Next i
ReplaceMe = strReturn
Else
Exit Function
End If
End Function
Create an Update query that includes the name of the field that you wish to
update. Start by creating a normal SELECT query. In query design view, click
on Query > Update Query to convert it into an update query. In the Update To:
row that you should see in the QBE grid, add the following statement:
ReplaceMe([FieldName])
where FieldName is the actual name of your field.
To prevent future data entry problems at the form level, you can investigate
the use of validation rules & text.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
:
Hi
How can I replace all characters except a-b,A-B,0-9 and hyphen from a form
field?
Thanks
Regards
