Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    8

    Unanswered: Exclude 9 Digit Part Numbers

    I am doing a query in Access 2010 in which I need to exclude all part numbers that have nine numbers in them (example 457896573). The part numbers are in a bill of materials. The nine digit part numbers are old part numbers that need to be excluded from the database. I tried using *<99999999* but the patt number *173405100* for instance is not being filtered out. I have part numbers such as "4722-0148" in the database which I want to remain but using <99999999 appears to not affect these part numbers. What I really need to do is eclude all 9 digit part numbers instead of using an expression such as <99999999. I apologize for asking a question that is probably a trivial thing for you database gurus but I have virtually no experience with databases.

    Thank you,

    Roger
    Last edited by ralexander; 10-24-12 at 08:26.

  2. #2
    Join Date
    Oct 2006
    Posts
    110
    Trying using Len()

    In your sql statement try something like
    example:

    select * from parts where Len(partnumber) <> 9


    That should return everything where the part number is not 9 characters.


    If you are using the access query design view, in your part number field the criteria would be len<>9.

  3. #3
    Join Date
    Oct 2012
    Posts
    8
    Thank you for your help. I tried using Not "Len 9" but for some reason I have the part numbers 173405100 and 173431200 aren't being excluded from the query. I have other queries to do and don't really want to sort through them and look for part numbers that aren't being excluded the ecluding specific part numbers. This shouldn't be a difficult thing to do really.

    Thanks again,

    Roger

  4. #4
    Join Date
    Oct 2006
    Posts
    110
    If you have len<>9 in your query criteria then it should work. Are you sure those numbers are only 9 characters and don't have any spaces at the beginning or end that you are unable to see?

    Try it in reverse too. try len=9 and see if it returns those numbers. If not, then they probably have spaces that you can't see.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where len(trim(mypartnumber))<>9
    shoudl do the trick
    the trim strips off leading and trailing spaces,
    the len <> 9 returns rows whoose part number is not equal 9 characters

    if you think you have a problem with trailing spaces run a one time update query to remove spaces


    eg
    UPDATE MyTable SET MyPartNumber = trim(MyPartnumber) WHERE len(MyPartNumber)<>9
    ...replace the MyTable, MyPartNumber with the names of your table and partnumber column respectively
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2012
    Posts
    8
    Thanks to everyone that helped me with this problem.

    Roger

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •