Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Filter based on first character of field

Discussion in 'Information Technology' started by Francophone, Jul 28, 2006.

  1. Francophone

    Francophone
    Expand Collapse
    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
     
  2. Loading...


  3. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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
     
  4. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "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. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________


    "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/expert_contributors.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
     
  6. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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" 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
    >
     
  8. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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,
     
  9. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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
     
  10. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

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

Share This Page