Delete any record with an alpha in a field

[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?)

 

[John Spencer]

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.

 

[Guest]

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

 

[David F Cox]

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.

 

[John Spencer]

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.

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

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?)