How To Identify Special Character

  • Thread starter Thread starter zyus
  • Start date Start date
Z

zyus

I want to identify type of special character in one of my IDNO field

IDNO SpecialC
A/1234 /
K*%1245 *%
K&1324 &
P@4567 @

Can i do it in query
 
On Sun, 28 Mar 2010 19:20:01 -0700, zyus

The InStr function can be used to test for certain substrings to occur
in the main string.

-Tom.
Microsoft Access MVP
 
I want to identify type of special character in one of my IDNO field

IDNO SpecialC
A/1234 /
K*%1245 *%
K&1324 &
P@4567 @

Can i do it in query
Click to expand...

There are 65536 possible UNICODE character. Which ones are special and which
aren't? Your definition of special might not agree with someone else's: e.g.
you might decide that "A" is legit but "a" is not (because it's lower case).

More info please!
 
Hi John,

What i'm looking at is only letter (A-Z) & number, other than that consider
special to my case..

Thanks
 
Hi Tom,

Thanks for your response. Frankly i don't understand what you mean as i'm
new to access plus i'm not a computer programmer. A simple comp language will
help.

A limited database on "string" words unable me to understand your comments
to the fullest. "substrings to occur in the main string" wow very tough to me.
 
You can identify the records using the LIKE operator in the criteria.

Field: IDNo
Criteria: LIKE "*[!0-9A-Z]*"

That should identify records where IDNo contains any character that is Not a
letter or number character.

If you need to list the characters, then the simplest way is to use a VBA
function. You can try the following UNTESTED function. Save it in a VBA
module and call it in your query.

Field: BadChars: fListSpecialChar([IdNo])

Public Function fListSpecialChar(vStrIN)
Dim vReturn as variant: vReturn = Null
Dim I as Long

If Trim(Len(vStrIn & "")) = 0 Then
vReturn = vStrIn
Else
For I = 1 to Len(vStrIn)
If Mid(vStrIn,I,1) Like "[!0-9A-Z]" THEN
vReturn = vReturn & Mid(vStrIn,I,1)
End if
Next I
END IF

fListSpecialChar = vReturn

END Function


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

The criteria LIKE "*[!0-9A-Z]*"
does it include records with spacing?

If it does, how to exclude spacing...Thanks

John Spencer said:
You can identify the records using the LIKE operator in the criteria.

Field: IDNo
Criteria: LIKE "*[!0-9A-Z]*"

That should identify records where IDNo contains any character that is Not a
letter or number character.

If you need to list the characters, then the simplest way is to use a VBA
function. You can try the following UNTESTED function. Save it in a VBA
module and call it in your query.

Field: BadChars: fListSpecialChar([IdNo])

Public Function fListSpecialChar(vStrIN)
Dim vReturn as variant: vReturn = Null
Dim I as Long

If Trim(Len(vStrIn & "")) = 0 Then
vReturn = vStrIn
Else
For I = 1 to Len(vStrIn)
If Mid(vStrIn,I,1) Like "[!0-9A-Z]" THEN
vReturn = vReturn & Mid(vStrIn,I,1)
End if
Next I
END IF

fListSpecialChar = vReturn

END Function


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

What i'm looking at is only letter (A-Z) & number, other than that consider
special to my case..

Thanks
Click to expand...
.
Click to expand...
 
How to call vba module in query?

John Spencer said:
You can identify the records using the LIKE operator in the criteria.

Field: IDNo
Criteria: LIKE "*[!0-9A-Z]*"

That should identify records where IDNo contains any character that is Not a
letter or number character.

If you need to list the characters, then the simplest way is to use a VBA
function. You can try the following UNTESTED function. Save it in a VBA
module and call it in your query.

Field: BadChars: fListSpecialChar([IdNo])

Public Function fListSpecialChar(vStrIN)
Dim vReturn as variant: vReturn = Null
Dim I as Long

If Trim(Len(vStrIn & "")) = 0 Then
vReturn = vStrIn
Else
For I = 1 to Len(vStrIn)
If Mid(vStrIn,I,1) Like "[!0-9A-Z]" THEN
vReturn = vReturn & Mid(vStrIn,I,1)
End if
Next I
END IF

fListSpecialChar = vReturn

END Function


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

What i'm looking at is only letter (A-Z) & number, other than that consider
special to my case..

Thanks
Click to expand...
.
Click to expand...
 
If there is a space in the IDNO then that record will be returned.

I gave you an example in my post. In a field rectangle, enter something like
the following:
BadChars: fListSpecialChar([IdNo])

If you need further help you might want to start a new thread. I am going to
be offline for the next few days.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
How to call vba module in query?

John Spencer said:
You can identify the records using the LIKE operator in the criteria.

Field: IDNo
Criteria: LIKE "*[!0-9A-Z]*"

That should identify records where IDNo contains any character that is Not a
letter or number character.

If you need to list the characters, then the simplest way is to use a VBA
function. You can try the following UNTESTED function. Save it in a VBA
module and call it in your query.

Field: BadChars: fListSpecialChar([IdNo])

Public Function fListSpecialChar(vStrIN)
Dim vReturn as variant: vReturn = Null
Dim I as Long

If Trim(Len(vStrIn & "")) = 0 Then
vReturn = vStrIn
Else
For I = 1 to Len(vStrIn)
If Mid(vStrIn,I,1) Like "[!0-9A-Z]" THEN
vReturn = vReturn & Mid(vStrIn,I,1)
End if
Next I
END IF

fListSpecialChar = vReturn

END Function


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

What i'm looking at is only letter (A-Z) & number, other than that consider
special to my case..

Thanks

:

I want to identify type of special character in one of my IDNO field

IDNO SpecialC
A/1234 /
K*%1245 *%
K&1324 &
P@4567 @

Can i do it in query
There are 65536 possible UNICODE character. Which ones are special and which
aren't? Your definition of special might not agree with someone else's: e.g.
you might decide that "A" is legit but "a" is not (because it's lower case).

More info please!
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top