How To Identify Special Character

[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

 

[Tom van Stiphout]

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

 

[John W. Vinson]

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!

 

[zyus]

Hi John,

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

Thanks

 

[zyus]

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.

 

[John Spencer]

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

 

[zyus]

Dear John

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

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

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

.

 

[zyus]

How to call vba module in query?

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

.

 

[John Spencer]

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?

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!

.