Filter based on first character of field

#1 (**permalink**) 28-Jul-2006, 08:36 AM

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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13350-filter-based-on-first-character-field.html

TIA

*

#2 (**permalink**) 28-Jul-2006, 08:36 AM

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.

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
HTH,
Nikos

#3 (**permalink**) 28-Jul-2006, 08:36 AM

Here is one method:

SELECT tblPartsData.[Part number]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
__________________________________________

"Francophone" wrote:

> 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

#4 (**permalink**) 28-Jul-2006, 08:36 AM

Oops. I misread your question. Change the criteria I used to = 0, if you want
to use my previous suggestion:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350

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

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
"Tom Wickerath" wrote:

> 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/ex...tributors.html
> http://www.access.qbuilt.com/html/search.html
> __________________________________________
>
> "Francophone" wrote:
>
> > 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

#5 (**permalink**) 28-Jul-2006, 08:36 AM

Tom,

Strictly speaking, this filters out non-numeric first characters, but
lets through other symbols printable characters like !@#$%^&*()_?>< etc,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
whereas the OP needs to filter through letters only.

Nikos

#6 (**permalink**) 28-Jul-2006, 08:36 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
__________________________________________

"Nikos Yannacopoulos" wrote:

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

#7 (**permalink**) 28-Jul-2006, 08:37 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
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,

#8 (**permalink**) 28-Jul-2006, 08:37 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
(because I've used it repeatedly), and trying to put that in a criterion
line, I guess...

Thanks for straightening this out.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350

Kind regards,

Nikos

#9 (**permalink**) 28-Jul-2006, 08:37 AM

Hi Nikos,

> I had no doubt you knew how to do it, just posted my comment for the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13350
> 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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Nikos Yannacopoulos" wrote:

> 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,