Filter based on first character of field

[Guest]

I need to perform a query where it picks out records only if the first
character of (ie. Part number) is a letter. How do I put this in a criteria?

TIA

 

[Nikos Yannacopoulos]

One way is to use a criterion like:

(>="A*" And <="Z*") Or (>="a*" And <="z*")

if you want both upper and lower case, or just the first part if you
only want upper case.

HTH,
Nikos

 

[Guest]

Here is one method:

SELECT tblPartsData.[Part number]
FROM tblPartsData
WHERE IsNumeric(Left([Part number],1))=-1;


where "tblPartsData" is the name of the table, and [Part number] is the name
of the field. Make the appropriate substitutions.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Oops. I misread your question. Change the criteria I used to = 0, if you want
to use my previous suggestion:

SELECT tblPartsData.[Part number]
FROM tblPartsData
WHERE IsNumeric(Left([Part number],1))=0;


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Here is one method:

SELECT tblPartsData.[Part number]
FROM tblPartsData
WHERE IsNumeric(Left([Part number],1))=-1;


where "tblPartsData" is the name of the table, and [Part number] is the name
of the field. Make the appropriate substitutions.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I need to perform a query where it picks out records only if the first
character of (ie. Part number) is a letter. How do I put this in a criteria?

TIA

 

[Nikos Yannacopoulos]

Tom,

Strictly speaking, this filters out non-numeric first characters, but
lets through other symbols printable characters like !@#$%^&*()_?>< etc,
whereas the OP needs to filter through letters only.

Nikos

 

[Guest]

Hi Nikos,

You are correct. Okay, how about this version?

SELECT tblPartsData.[Part number]
FROM tblPartsData
WHERE Asc(Left([Part number],1)) Between 65 And 90
Or Asc(Left([Part number],1)) Between 97 And 122

PS. On your submission, this part seems to be incorrect:

"or just the first part if you only want upper case."


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Nikos Yannacopoulos]

Tom,

This is the way I would have done it in VBA. I proposed the other
approach because, while functionally equivalent, it fits right in the
criterion line in the query design grid, without the need for calculated
fields... so my answer would be as close as possible to the original
question "How do I put this in a criteria".

I had no doubt you knew how to do it, just posted my comment for the
benefit of less experienced readers.

Kind regards,

 

[Nikos Yannacopoulos]

Hi again Tom,

I was puzzled for a minute by your PS, then I realized the proposed
filtering is not case-sensitive, so just:

="A*" And <="Z*"

would have done it. I think I had your solution in the back of my mind
(because I've used it repeatedly), and trying to put that in a criterion
line, I guess...

Thanks for straightening this out.

Kind regards,

Nikos

 

[Guest]

Hi Nikos,

I had no doubt you knew how to do it, just posted my comment for the
benefit of less experienced readers.

That's fine. Your method works perfectly good too. I specifically said "Here
is one method" because I know that there is more than one way to skin a cat
in this case. The problem I had is that I initially misinterpreted the
request, so I was looking for records that started with a number, not a
letter. I'm just more comfortable calling the IsNumeric function in that case
(which, as you've pointed out, is not required since we're looking for an
alpha character instead).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________