detecting unwanted characters

[AccessMan]

I need a query criterion that returns records in which a text field (255 max
characters) contains any character(s) other than alpha (upper and lower
case), digit, period, dash, or underscore. The text field lengths are not
constant. I'm striking out tying to do this with the functions that I see
available, but I'm not at all convinced that it can't be done. Any
suggestions?

Thanks!

 

[KARL DEWEY]

UNTESTED --
Create a table with one field, TXT, containing all of the characters that
are acceptable.
In a select query with both tables not joined add a calculated field like
this --
Test_it: InStr([YourField], [TXT])
Set criteria >0 on this calculated field.

 

[John Spencer]

You might try the following

SELECT TextField
FROM SomeTable
WHERE TextField Like "*[!._0-9,a-z-]*"

Basically that should find any text that has at least one of the
characters is NOT a period, underscore,letter, number of dash.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[AccessMan]

Karl:

This suggestion is successful in finding the presence of acceptable
characters anywhere in the field value, but I'm looking for the presence of
UNacceptable characters, preferably without having to enumerate them.

UNTESTED --
Create a table with one field, TXT, containing all of the characters that
are acceptable.
In a select query with both tables not joined add a calculated field like
this --
Test_it: InStr([YourField], [TXT])
Set criteria >0 on this calculated field.
--
KARL DEWEY
Build a little - Test a little

I need a query criterion that returns records in which a text field (255 max
characters) contains any character(s) other than alpha (upper and lower
case), digit, period, dash, or underscore. The text field lengths are not
constant. I'm striking out tying to do this with the functions that I see
available, but I'm not at all convinced that it can't be done. Any
suggestions?

Thanks!

 

[John Spencer]

Did you try the query I suggested?

If so, did it give you the wrong results? Or did it error?

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Karl:

This suggestion is successful in finding the presence of acceptable
characters anywhere in the field value, but I'm looking for the presence of
UNacceptable characters, preferably without having to enumerate them.

UNTESTED --
Create a table with one field, TXT, containing all of the characters that
are acceptable.
In a select query with both tables not joined add a calculated field like
this --
Test_it: InStr([YourField], [TXT])
Set criteria >0 on this calculated field.
--
KARL DEWEY
Build a little - Test a little

I need a query criterion that returns records in which a text field (255 max
characters) contains any character(s) other than alpha (upper and lower
case), digit, period, dash, or underscore. The text field lengths are not
constant. I'm striking out tying to do this with the functions that I see
available, but I'm not at all convinced that it can't be done. Any
suggestions?

Thanks!

 

[John]

I actually had a need for a similar thing awhile ago, but in mine, I
wanted to actually remove the unwanted characters rather than just
detect their presence. I wrote a function that takes the string you want
to check for unwanted characters as well as a string containing the
characters that you DO want, and it loops through the first string
removing each character that did NOT appear in the second string. Please
note that the following functions have not been tested, and I have a
habit of mixing up the order of the string parameters in the InStr
function, so I'm not sure if I got them right.


This function should return a string containing only the characters you
WANT to allow:

Public Function StripUnwantedCharacters(stringToCheck as string,
allowedChars as string) as string

Dim tmp as string
dim i as integer

for i = 1 to len(stringToCheck)
if instr(mid(stringToCheck, i, 1), allowedChars, 1) > 0 then
tmp = tmp & mid(stringToCheck, i, 1)
end if
next i

StripUnwantedCharacters = tmp

end function



You could modify it to simply return a flag if a field contains unwanted
characters, maybe something like this:


Public Function FlagUnwantedCharacters(stringToCheck as string,
allowedChars as string) as boolean

Dim returnValue as boolean
dim i as integer

returnValue = false

for i = 1 to len(stringToCheck)
if instr(mid(stringToCheck, i, 1), allowedChars, 1) > 0 then
returnValue = true
i = len(stringToCheck) + 1
end if
next i

FlagUnwantedCharacters = returnValue

end function



Then in your query, you would say something like:

SELECT * FROM [TableName] WHERE FlagUnwantedCharacters([FieldName],
"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_") = True;



I realize that the second parameter of the function is a bit long, but
doing it this way will allow you to use the function for different sets
of allowed characters.

 

[AccessMan]

John:

I tried the other suggestion first. When I tried your suggestion it did
work - thanks! I'm a little mystified about the syntax of

WHERE TextField Like "*[!._0-9,a-z-]*"

I see that the ! serves as NOT. and it seems to apply to everything that
follows. Is the comma treated as a separator or as member of the set?
Separators don't seem to be needed after the period, underscore, and the a-z
sequence. Is this syntax described somewhere?

Thanks!

Did you try the query I suggested?

If so, did it give you the wrong results? Or did it error?

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Karl:

This suggestion is successful in finding the presence of acceptable
characters anywhere in the field value, but I'm looking for the presence of
UNacceptable characters, preferably without having to enumerate them.

UNTESTED --
Create a table with one field, TXT, containing all of the characters that
are acceptable.
In a select query with both tables not joined add a calculated field like
this --
Test_it: InStr([YourField], [TXT])
Set criteria >0 on this calculated field.
--
KARL DEWEY
Build a little - Test a little


:

I need a query criterion that returns records in which a text field (255 max
characters) contains any character(s) other than alpha (upper and lower
case), digit, period, dash, or underscore. The text field lengths are not
constant. I'm striking out tying to do this with the functions that I see
available, but I'm not at all convinced that it can't be done. Any
suggestions?

Thanks!

 

[John Spencer]

Actually the comma is not needed as a separator.

The syntax is described in the Access help-Enter Like into the search
box. You can also look up the help for LIKE in the VBA help. Access
Jet SQL works almost exactly the same way.

I should have stuck the comma earlier in the sequence (if comma is a
valid character or I should have left the comma out completely.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John:

I tried the other suggestion first. When I tried your suggestion it did
work - thanks! I'm a little mystified about the syntax of

WHERE TextField Like "*[!._0-9,a-z-]*"

I see that the ! serves as NOT. and it seems to apply to everything that
follows. Is the comma treated as a separator or as member of the set?
Separators don't seem to be needed after the period, underscore, and the a-z
sequence. Is this syntax described somewhere?

Thanks!

Did you try the query I suggested?

If so, did it give you the wrong results? Or did it error?

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Karl:

This suggestion is successful in finding the presence of acceptable
characters anywhere in the field value, but I'm looking for the presence of
UNacceptable characters, preferably without having to enumerate them.


:

UNTESTED --
Create a table with one field, TXT, containing all of the characters that
are acceptable.
In a select query with both tables not joined add a calculated field like
this --
Test_it: InStr([YourField], [TXT])
Set criteria >0 on this calculated field.
--
KARL DEWEY
Build a little - Test a little


:

I need a query criterion that returns records in which a text field (255 max
characters) contains any character(s) other than alpha (upper and lower
case), digit, period, dash, or underscore. The text field lengths are not
constant. I'm striking out tying to do this with the functions that I see
available, but I'm not at all convinced that it can't be done. Any
suggestions?

Thanks!

 

[AccessMan]

John - thanks again, I am using this to great advantage now.

One strange thing though: using like "*[!._0-9a-z-]*" does not flag the
greek symbol phi Ø as disallowed.

Actually the comma is not needed as a separator.

The syntax is described in the Access help-Enter Like into the search
box. You can also look up the help for LIKE in the VBA help. Access
Jet SQL works almost exactly the same way.

I should have stuck the comma earlier in the sequence (if comma is a
valid character or I should have left the comma out completely.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John:

I tried the other suggestion first. When I tried your suggestion it did
work - thanks! I'm a little mystified about the syntax of

WHERE TextField Like "*[!._0-9,a-z-]*"

I see that the ! serves as NOT. and it seems to apply to everything that
follows. Is the comma treated as a separator or as member of the set?
Separators don't seem to be needed after the period, underscore, and the a-z
sequence. Is this syntax described somewhere?

Thanks!

Did you try the query I suggested?

If so, did it give you the wrong results? Or did it error?

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


AccessMan wrote:
Karl:

This suggestion is successful in finding the presence of acceptable
characters anywhere in the field value, but I'm looking for the presence of
UNacceptable characters, preferably without having to enumerate them.


:

UNTESTED --
Create a table with one field, TXT, containing all of the characters that
are acceptable.
In a select query with both tables not joined add a calculated field like
this --
Test_it: InStr([YourField], [TXT])
Set criteria >0 on this calculated field.
--
KARL DEWEY
Build a little - Test a little


:

I need a query criterion that returns records in which a text field (255 max
characters) contains any character(s) other than alpha (upper and lower
case), digit, period, dash, or underscore. The text field lengths are not
constant. I'm striking out tying to do this with the functions that I see
available, but I'm not at all convinced that it can't be done. Any
suggestions?

Thanks!