Searching for non-alphanumeric characters

[Guest]

I have a field that contains batch numbers. Apparently, they were created a
while back and I've been told they are Cobol numbers. However, they give
problems in our system because they contain symbols like !, $, & etc.

I have to mark all the ones with symbols in them as "Do not Assign".

I am trying to figure out how to update all the records in a query. The
batch numbers are 3 digits long and each one starts with a letter or number,
but the second &/or third position character can contain a symbol (ie - R@1,
1!~, 1!E, etc)

Is there a way I can run an update query to note another field as do not
assign?

I've been using a series of Like "*@" statemetns
in the criteria and because each they contain the wildcard symbol, I get an
error when I try to turn it into an update query.

TIA!

 

[John Spencer]

TRY the following

UPDATE YourTable
SET DoNotAssign = True
WHERE COBOLNumber Not Like "[0-9A-z][0-9A-z][0-9A-z]"

 

[Guest]

Hi John,

Thanks for your reply. I just figured ran a query that worked, although it
wasn't as slick as your idea. I first ran a query with the condition of Like
"*[!0-9A-Z]*" to get the results of all the non-alphanumerics. Then I
exported the data to a table and ran an update statement to it.

Whew...glad that's over. There were over 5000 records to update.

TRY the following

UPDATE YourTable
SET DoNotAssign = True
WHERE COBOLNumber Not Like "[0-9A-z][0-9A-z][0-9A-z]"

I have a field that contains batch numbers. Apparently, they were created
a
while back and I've been told they are Cobol numbers. However, they give
problems in our system because they contain symbols like !, $, & etc.

I have to mark all the ones with symbols in them as "Do not Assign".

I am trying to figure out how to update all the records in a query. The
batch numbers are 3 digits long and each one starts with a letter or
number,
but the second &/or third position character can contain a symbol (ie -
R@1,
1!~, 1!E, etc)

Is there a way I can run an update query to note another field as do not
assign?

I've been using a series of Like "*@" statemetns
in the criteria and because each they contain the wildcard symbol, I get
an
error when I try to turn it into an update query.

TIA!