Searching for a digit or letter only

[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

 

[XPS350]

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

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

 

[Clifford Bass via AccessMonster.com]

Hi,

Try this in the criteria line:

Like "6[A-Z]?"

Clifford Bass

 

[Clifford Bass via AccessMonster.com]

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

Hi,

Try this in the criteria line:

Like "6[A-Z]?"

Clifford Bass

 

[KARL DEWEY]

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];

 

[Grd]

This is excellent. Very efficient.

Tx

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

Hi,

Try this in the criteria line:

Like "6[A-Z]?"

Clifford Bass

 

[Clifford Bass via AccessMonster.com]

Hi Suzanne,

You are welcome.

Clifford Bass

This is excellent. Very efficient.

Tx