Delete any record with an alpha in a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with a field that was imported as text and need to delete out
any record where this field contains an alpha digit in any one of the 7
characters in the field.

Short of examining each character and deleting the record if the character
is not in 0-9....?

The table is too large (400K records) to change the data type in design view
(again apart from splitting, changing data type etc?)
 
Try this Select statement to see if it identifies the records you want to
delete

SELECT *
FROM YourTable
WHERE YourField Like "*[!0-9]*"

That won't catch records that have all digits, but are not seven characters
in length. For instance "123" will pass the test.

Alternative
SELECT *
FROM YourTable
WHERE YourField Not Like "#######"

That will only work if your field is exactly seven characters long.


That basically should get any records where the field contains any character
that is not a number character.
 
Hi Andy

I'm not a great believer in simply deleteing loads of records without
looking at them first.

You could create a new query and use this as a criteria then you will be
able to see the record and choose to delete them or not.

Like "*[A-Z]*"


Hope this helps
 
one possibility:
if you change the data type to number access will delete all invalid fields.
that may be sufficient to identify all records that have to be deleted.
 
Your criteria works to spot records with any letter in them, but it won't
find those with spaces, punctuation marks, or other non-letter characters.

I believe it would fail to find the following.
"-000123"
"000 123"
"$123456"


The poster knows the data, so should be able to decide which of the options
posted would work in this situation.

Wayne-I-M said:
Hi Andy

I'm not a great believer in simply deleteing loads of records without
looking at them first.

You could create a new query and use this as a criteria then you will be
able to see the record and choose to delete them or not.

Like "*[A-Z]*"


Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do


Andy said:
I have a table with a field that was imported as text and need to delete
out
any record where this field contains an alpha digit in any one of the 7
characters in the field.

Short of examining each character and deleting the record if the
character
is not in 0-9....?

The table is too large (400K records) to change the data type in design
view
(again apart from splitting, changing data type etc?)
Click to expand...
Click to expand...
 
Back
Top