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!

 

[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!