Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2002
    Posts
    22

    Unanswered: How do you perform a last digit lookup?

    Hello, I am trying to find you how do you perform a last digit lookup using a query.

    For example I have a table with employee numbers and I need to find the ones that end with the number 6

    Thank you for your help and time.

    mojo-jim

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    lets say that the employe number is standard at 6 char. long
    to look up the last one in a query grid select the column employee number
    do a mid on the column
    mid(employee_num,6,1) (thats mid starting at the 6th place going one place)
    then in the criteria put in the value that you want to search for
    ie the number 6

    that should do it in a normal query grid
    Jim

  3. #3
    Join Date
    Sep 2002
    Posts
    22
    Thank you for your response, My question is where do I put the mid([employee]6,1,) statment does it go in the first rown of the query grid or some where else. Thank you again,

  4. #4
    Join Date
    Aug 2002
    Location
    Singapore
    Posts
    91
    I think you can put Right([employee], 1)...
    Depend on what do you want as a result. On the query grid, just use the new column, put field: Right([employee], 1), and put criteria: 6, (or whatever value), then uncheck the "show" unless you want another field that display the last digit. Usually, access will move the unshow field to the right.

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    attatched is a bmp that shows where to put it as criteria
    There are many ways to do it, but I think this is the best way to use mid as a filter
    Regards

  6. #6
    Join Date
    Sep 2002
    Posts
    22
    When I try that right([employee],1) statment I get a #error in the result. If I try to add a criteria to the query column I get a error message statment may be too complex please simplify.

    Still seaking more options
    Thank you

  7. #7
    Join Date
    Nov 2001
    Posts
    336
    Is there any chance that [employee] field has Null value?

    If yes, then modify your query using Right(NZ([employee],"0"),1)

    HTH, Igor

  8. #8
    Join Date
    Sep 2002
    Posts
    22
    no chance of null's I am sure of that

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe employee is a numeric?

    select [employee]
    from yourtable
    where right(str([employee]),1)='6'


    rudy
    http://rudy.ca/

  10. #10
    Join Date
    Sep 2002
    Posts
    22
    no chance of null's I am sure of that

  11. #11
    Join Date
    Aug 2002
    Location
    Singapore
    Posts
    91
    I think Right([employee], 1) will not produce any error, and not too complex, provided [employee] is the correct field name to look up... and you are very sure there is no chance of null's...
    The criteria should be a text: i.e "6", not just 6...

    Which version of Access do you use?

  12. #12
    Join Date
    Sep 2002
    Posts
    22
    I am using access 97

Posting Permissions

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