Searching for a digit or letter only

  • Thread starter Thread starter Grd
  • Start date Start date
G

Grd

HI there,

I need to query a product_code which is a text column. I want all results
where:
The first 'letter' is a 6.
The second letter must be a letter (not a number).
The third has to be a letter or number i.e. The product code has to be 3
characters long.

Could someone help with the criteria needed for this.

Any help would be greatly appreciated.

Tx

Suzanne
 
HI there,

I need to query a product_code which is a text column. I want all results
where:
The first 'letter' is a 6.
The second letter must be a letter (not a number).
The third has to be a letter or number i.e. The product code has to be 3
characters long.

Could someone help with the criteria needed for this.

Any help would be greatly appreciated.

Tx

Suzanne
Click to expand...

That would look like:

SELECT * FROM YourTabel
WHERE Left(product_code,1)="6" AND NOT IsNumeric(Mid(product_code,
2,1)) AND Len(product_code)=3

Groeten,

Peter
http://access.xps350.com
 
Try this --
SELECT YourTable.*
FROM YourTable
WHERE Left([product_code],1) = "6" AND (Mid([product_code],2,1) Between "A"
AND "Z") AND Mid([product_code],3,1) Between "0" AND"9" AND
Mid([product_code],3,1) Between "A" AND "Z"
ORDER BY [product_code];
 
This is excellent. Very efficient.

Tx

Clifford Bass via AccessMonster.com said:
Hi,

That assumed the third was always a letter or number. If it can be
something else try:

Like "6[A-Z][A-Z,0-9]"

Clifford Bass

Clifford said:
Hi,

Try this in the criteria line:

Like "6[A-Z]?"

Clifford Bass
Click to expand...
Click to expand...
 
Back
Top