A query to discover the number

[DRANDON]

All, I am in Access and need a way in a query discover if all of my 5 characters are text, then delete the record. I could have values such as "E3MTH" or "DOS6P", but if my query comes across a value that is like "DTHRS" delete the record. I was thinking something like

iif(and(Not Numeric(Mid([CPT],1),Not Numeric(Mid([CPT],2),Not Numeric(Mid([CPT],3),Not Numeric(Mid([CPT],4),Not Numeric(Mid([CPT],5))

I will be trying this out in Access tomorrow but I thought I would throw this out to my favorite forum for ideas. Thanks so much in advance.

 

[Norie]

You could use Like perhaps.

UCase([CPT]) Like "[A-Z][A-Z][A-Z][A-Z][A-Z]"

 

[Micron]

OK as long as you only ever have 5 characters in the field value. Otherwise, you might want to use a sub or function to evaluate each character.