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

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

On Mon, 3 Nov 2008 16:38:02 -0800, zyus <(E-Mail Removed)>
wrote:

>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?
--

John W. Vinson [MVP]

On Nov 3, 7:46*pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Mon, 3 Nov 2008 16:38:02 -0800, zyus <z...@discussions.microsoft.com>
> wrote:
>
> >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 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?
> --
>
> * * * * * * *John W. Vinson [MVP]

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

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" wrote:

> On Mon, 3 Nov 2008 16:38:02 -0800, zyus <(E-Mail Removed)>
> wrote:
>
> >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?
> --
>
> John W. Vinson [MVP]
>

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
'====================================================


zyus wrote:
> 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" wrote:
>
>> On Mon, 3 Nov 2008 16:38:02 -0800, zyus <(E-Mail Removed)>
>> wrote:
>>
>>> 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?
>> --
>>
>> John W. Vinson [MVP]
>>

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

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

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
'====================================================


Fred wrote:
> How 'bout just using the "replace" function to replace each of these
> characters with nothing.
>
> e.g. replace([fieldname],"/","")
>
>

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" <(E-Mail Removed)> wrote in message
news:E3EDB65F-F64F-4081-A264-(E-Mail Removed)...
> In a delete query...how to delete any special character in a field...
>
> Special character could be :;,.-\ / + etc...
>
>

Tried but got syntax error

"Klatuu" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:E3EDB65F-F64F-4081-A264-(E-Mail Removed)...
> > In a delete query...how to delete any special character in a field...
> >
> > Special character could be :;,.-\ / + etc...
> >
> >
>
>
>

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:

> 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
> '====================================================
>
>
> zyus wrote:
> > 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" wrote:
> >
> >> On Mon, 3 Nov 2008 16:38:02 -0800, zyus <(E-Mail Removed)>
> >> wrote:
> >>
> >>> 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?
> >> --
> >>
> >> John W. Vinson [MVP]
> >>
>