How to delete special character

Z

zyus

In a delete query...how to delete any special character in a field...

Special character could be :;,.-\ / + etc...
 
J

John W. Vinson

In a delete query...how to delete any special character in a field...

Special character could be :;,.-\ / + etc...
Click to expand...

A Delete query does NOT delete characters in a field!!!!! It deletes *entire
records*.

I think what you want is an Update query. It'll be a somewhat complicated one
since you are painting with a broad brush here. Could you explain the actual
contents of the field, and what final result you want, perhaps with some
examples?
 
P

pietlinden

A Delete query does NOT delete characters in a field!!!!! It deletes *entire
records*.

I think what you want is an Update query. It'll be a somewhat complicatedone
since you are painting with a broad brush here. Could you explain the actual
contents of the field, and what final result you want, perhaps with some
examples?
Click to expand...

or a series of update SQL statements where the search characters are
in a table... would make it a lot easier to manage.
 
Z

zyus

Sorry John for my blur explaination...

You're right that the query that i meant is update query and not delete query.

I have a field name IDNO(text) that consist records with special character
such as - / + spacing + and etc.

What i want to do is to delete all the special character leaving only alpha
and number only.

I've used this function Replace([IDNO],"-","") in update query but i've to
trigger every each special character (spacing) that i can expect.

I used access for data reporting and analysis and i've to use the update
query to clean the IDNO field.

Is there any trick where i can just trigger one function that can delete all
the special character.

Hope this is clear
 
J

John Spencer

There is no built-in function to do this. You would need to create a
custom VBA function to examine each character and keep just the ones you
want.

Public Function fStripToLtrNum(strIn)
Dim strOut as String
Dim iPos as Long

If Len(StrIn & "") > 0 then
For iPos = 1 to Len(StrIN)
If Mid(strIn,iPos,1) Like "0-9A-z" then
strOut = strOut & Mid(strIn,iPos)
End If
Next iPos
End If

If Len(StrOut) = 0 Then
fStripToLtrNum = Null
Else
fStripToLtrNum = StrOut
End If
End Function

Your update query might look like the following
UPDATE YourTable
SET IDNo = fStripToLtrNum([IdNo])
WHERE IdNO Like "*[!0-9A-z]*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Sorry John for my blur explaination...

You're right that the query that i meant is update query and not delete query.

I have a field name IDNO(text) that consist records with special character
such as - / + spacing + and etc.

What i want to do is to delete all the special character leaving only alpha
and number only.

I've used this function Replace([IDNO],"-","") in update query but i've to
trigger every each special character (spacing) that i can expect.

I used access for data reporting and analysis and i've to use the update
query to clean the IDNO field.

Is there any trick where i can just trigger one function that can delete all
the special character.

Hope this is clear

John W. Vinson said:
A Delete query does NOT delete characters in a field!!!!! It deletes *entire
records*.

I think what you want is an Update query. It'll be a somewhat complicated one
since you are painting with a broad brush here. Could you explain the actual
contents of the field, and what final result you want, perhaps with some
examples?
Click to expand...
Click to expand...
 
F

Fred

How 'bout just using the "replace" function to replace each of these
characters with nothing.

e.g. replace([fieldname],"/","")
 
J

John Spencer

You could but that would be a LOT of replace operations. If I
understood the original posting correctly, the characters to delete
could be well in excess of 100. Just for starters, the poster seemed to
want to get rid of any of the following characters (all of which can
easily be typed with the keyboard.
!@#$%^&*()_+=-{}[]::;'"`~?/><,.Space

In addition there are all the special characters that can be entered.
And if I was really paranoid, I would have compared Ascii ranges to make
sure the accented, grave, and umlauted characters did not get accepted
by the routine.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
K

Klatuu

Here is a function you can use for that purpose

'---------------------------------------------------------------------------------------
' Procedure : CharsAndNums
' DateTime : 10/15/2005 11:00
' Author : Dave Hargis
' Purpose : Removes all characters from a string that are not digits or
alpha characters
' : That is a - Z, A - Z, and 0 - 9
'---------------------------------------------------------------------------------------
'
Public Function CharsAndNums(strJunk As String) As String
Dim lngCtr As Long
Dim intAscii As Integer

For lngCtr = 1 To Len(strJunk)
intAscii = Asc(Mid(strJunk, lngCtr, 1))
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Then
CharsAndNums = CharsAndNums & Mid(strJunk, lngCtr, 1)
End If
Next lngCtr

End Function

But, a delete query deletes records. I think you mean an update query that
changes the value in a field, deleting special characters. If that is the
case this will do it. To use a function in a query, it must be in a
standard module. In the query builder, it would look like this in the
Update To row:

CharsAndNums([MyFieldName])
 
Z

zyus

Tried but got syntax error

Klatuu said:
Here is a function you can use for that purpose

'---------------------------------------------------------------------------------------
' Procedure : CharsAndNums
' DateTime : 10/15/2005 11:00
' Author : Dave Hargis
' Purpose : Removes all characters from a string that are not digits or
alpha characters
' : That is a - Z, A - Z, and 0 - 9
'---------------------------------------------------------------------------------------
'
Public Function CharsAndNums(strJunk As String) As String
Dim lngCtr As Long
Dim intAscii As Integer

For lngCtr = 1 To Len(strJunk)
intAscii = Asc(Mid(strJunk, lngCtr, 1))
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Then
CharsAndNums = CharsAndNums & Mid(strJunk, lngCtr, 1)
End If
Next lngCtr

End Function

But, a delete query deletes records. I think you mean an update query that
changes the value in a field, deleting special characters. If that is the
case this will do it. To use a function in a query, it must be in a
standard module. In the query builder, it would look like this in the
Update To row:

CharsAndNums([MyFieldName])
zyus said:
In a delete query...how to delete any special character in a field...

Special character could be :;,.-\ / + etc...
Click to expand...
Click to expand...
 
Z

zyus

Hi John,

The update query based on the VBA function deleted not only the special
character but all records (alpha & number) that matched the criteria....

John Spencer said:
There is no built-in function to do this. You would need to create a
custom VBA function to examine each character and keep just the ones you
want.

Public Function fStripToLtrNum(strIn)
Dim strOut as String
Dim iPos as Long

If Len(StrIn & "") > 0 then
For iPos = 1 to Len(StrIN)
If Mid(strIn,iPos,1) Like "0-9A-z" then
strOut = strOut & Mid(strIn,iPos)
End If
Next iPos
End If

If Len(StrOut) = 0 Then
fStripToLtrNum = Null
Else
fStripToLtrNum = StrOut
End If
End Function

Your update query might look like the following
UPDATE YourTable
SET IDNo = fStripToLtrNum([IdNo])
WHERE IdNO Like "*[!0-9A-z]*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Sorry John for my blur explaination...

You're right that the query that i meant is update query and not delete query.

I have a field name IDNO(text) that consist records with special character
such as - / + spacing + and etc.

What i want to do is to delete all the special character leaving only alpha
and number only.

I've used this function Replace([IDNO],"-","") in update query but i've to
trigger every each special character (spacing) that i can expect.

I used access for data reporting and analysis and i've to use the update
query to clean the IDNO field.

Is there any trick where i can just trigger one function that can delete all
the special character.

Hope this is clear

John W. Vinson said:
In a delete query...how to delete any special character in a field...

Special character could be :;,.-\ / + etc...

A Delete query does NOT delete characters in a field!!!!! It deletes *entire
records*.

I think what you want is an Update query. It'll be a somewhat complicated one
since you are painting with a broad brush here. Could you explain the actual
contents of the field, and what final result you want, perhaps with some
examples?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

My error. I should have tested the code instead of typing it on the fly.
I left out a set of brackets in one line (like comparison) and left out the
third argument in the Mid function in one line

Try this. I have minimally tested it.

Public Function fStripToLtrNum(strIn)
'Strips out all non-number or non-letter characters
Dim strOut As String
Dim iPos As Long

If Len(strIn & "") > 0 Then
For iPos = 1 To Len(strIn)
If Mid(strIn, iPos, 1) Like "[0-9A-z]" Then
strOut = strOut & Mid(strIn, iPos, 1)
End If
Next iPos
End If

If Len(strOut) = 0 Then
fStripToLtrNum = Null
Else
fStripToLtrNum = strOut
End If
End Function

Also, I forgot to mention that you need to backup your data before doing an
update in case something goes wrong. I do apologize for that. I do hope that
you did backup before attempting to update.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
K

Klatuu

Where did you get the error?
I tested it before I posted it.

zyus said:
Tried but got syntax error

Klatuu said:
Here is a function you can use for that purpose

'---------------------------------------------------------------------------------------
' Procedure : CharsAndNums
' DateTime : 10/15/2005 11:00
' Author : Dave Hargis
' Purpose : Removes all characters from a string that are not digits or
alpha characters
' : That is a - Z, A - Z, and 0 - 9
'---------------------------------------------------------------------------------------
'
Public Function CharsAndNums(strJunk As String) As String
Dim lngCtr As Long
Dim intAscii As Integer

For lngCtr = 1 To Len(strJunk)
intAscii = Asc(Mid(strJunk, lngCtr, 1))
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Then
CharsAndNums = CharsAndNums & Mid(strJunk, lngCtr, 1)
End If
Next lngCtr

End Function

But, a delete query deletes records. I think you mean an update query
that
changes the value in a field, deleting special characters. If that is
the
case this will do it. To use a function in a query, it must be in a
standard module. In the query builder, it would look like this in the
Update To row:

CharsAndNums([MyFieldName])
zyus said:
In a delete query...how to delete any special character in a field...

Special character could be :;,.-\ / + etc...
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

As a guess on one of the lines that wrapped in my news reader

If so, then I would suspect that it needs to revised as follows
'---------------------------------------------------------------------------------------
' Procedure : CharsAndNums
' DateTime : 10/15/2005 11:00
' Author : Dave Hargis
' Purpose : Removes all characters from a string that are not digits or
alpha characters
' : That is a - Z, A - Z, and 0 - 9
'---------------------------------------------------------------------------------------
'
Public Function CharsAndNums(strJunk As String) As String
Dim lngCtr As Long
Dim intAscii As Integer

For lngCtr = 1 To Len(strJunk)
intAscii = Asc(Mid(strJunk, lngCtr, 1))
If (intAscii >= 48 And intAscii <= 57) _
Or (intAscii >= 65 And intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Then
CharsAndNums = CharsAndNums & Mid(strJunk, lngCtr, 1)
End If
Next lngCtr

End Function

The If wrapped into three lines and read

If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Then

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Where did you get the error?
I tested it before I posted it.

zyus said:
Tried but got syntax error

Klatuu said:
Here is a function you can use for that purpose

'---------------------------------------------------------------------------------------
' Procedure : CharsAndNums
' DateTime : 10/15/2005 11:00
' Author : Dave Hargis
' Purpose : Removes all characters from a string that are not digits or
alpha characters
' : That is a - Z, A - Z, and 0 - 9
'---------------------------------------------------------------------------------------
'
Public Function CharsAndNums(strJunk As String) As String
Dim lngCtr As Long
Dim intAscii As Integer

For lngCtr = 1 To Len(strJunk)
intAscii = Asc(Mid(strJunk, lngCtr, 1))
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Then
CharsAndNums = CharsAndNums & Mid(strJunk, lngCtr, 1)
End If
Next lngCtr

End Function

But, a delete query deletes records. I think you mean an update query
that
changes the value in a field, deleting special characters. If that is
the
case this will do it. To use a function in a query, it must be in a
standard module. In the query builder, it would look like this in the
Update To row:

CharsAndNums([MyFieldName])
In a delete query...how to delete any special character in a field...

Special character could be :;,.-\ / + etc...
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

That would be correct, John.

John Spencer said:
As a guess on one of the lines that wrapped in my news reader

If so, then I would suspect that it needs to revised as follows
'---------------------------------------------------------------------------------------
' Procedure : CharsAndNums
' DateTime : 10/15/2005 11:00
' Author : Dave Hargis
' Purpose : Removes all characters from a string that are not digits or
alpha characters
' : That is a - Z, A - Z, and 0 - 9
'---------------------------------------------------------------------------------------
'
Public Function CharsAndNums(strJunk As String) As String
Dim lngCtr As Long
Dim intAscii As Integer

For lngCtr = 1 To Len(strJunk)
intAscii = Asc(Mid(strJunk, lngCtr, 1))
If (intAscii >= 48 And intAscii <= 57) _
Or (intAscii >= 65 And intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Then
CharsAndNums = CharsAndNums & Mid(strJunk, lngCtr, 1)
End If
Next lngCtr

End Function

The If wrapped into three lines and read

If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Then

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Where did you get the error?
I tested it before I posted it.

zyus said:
Tried but got syntax error

:

Here is a function you can use for that purpose

'---------------------------------------------------------------------------------------
' Procedure : CharsAndNums
' DateTime : 10/15/2005 11:00
' Author : Dave Hargis
' Purpose : Removes all characters from a string that are not digits
or
alpha characters
' : That is a - Z, A - Z, and 0 - 9
'---------------------------------------------------------------------------------------
'
Public Function CharsAndNums(strJunk As String) As String
Dim lngCtr As Long
Dim intAscii As Integer

For lngCtr = 1 To Len(strJunk)
intAscii = Asc(Mid(strJunk, lngCtr, 1))
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Then
CharsAndNums = CharsAndNums & Mid(strJunk, lngCtr, 1)
End If
Next lngCtr

End Function

But, a delete query deletes records. I think you mean an update query
that
changes the value in a field, deleting special characters. If that is
the
case this will do it. To use a function in a query, it must be in a
standard module. In the query builder, it would look like this in the
Update To row:

CharsAndNums([MyFieldName])
In a delete query...how to delete any special character in a field...

Special character could be :;,.-\ / + etc...
Click to expand...
Click to expand...
Click to expand...
 
Z

zyus

Thanks John... It's work perfectly. Now i dont need to remember all the
special character...

No need to apologise as i did the testing on my backup data

John Spencer said:
My error. I should have tested the code instead of typing it on the fly.
I left out a set of brackets in one line (like comparison) and left out the
third argument in the Mid function in one line

Try this. I have minimally tested it.

Public Function fStripToLtrNum(strIn)
'Strips out all non-number or non-letter characters
Dim strOut As String
Dim iPos As Long

If Len(strIn & "") > 0 Then
For iPos = 1 To Len(strIn)
If Mid(strIn, iPos, 1) Like "[0-9A-z]" Then
strOut = strOut & Mid(strIn, iPos, 1)
End If
Next iPos
End If

If Len(strOut) = 0 Then
fStripToLtrNum = Null
Else
fStripToLtrNum = strOut
End If
End Function

Also, I forgot to mention that you need to backup your data before doing an
update in case something goes wrong. I do apologize for that. I do hope that
you did backup before attempting to update.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi John,

The update query based on the VBA function deleted not only the special
character but all records (alpha & number) that matched the criteria....

"John Spencer" wrote:
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Special characters 3
How To Identify Special Character 8
importing special characters 4
special characters 6
Record find with special characters 1
Access Functions 5
What character can I use to force a record to come after Z in sort 9
Find and replace new line character 1

Top