Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Bulgaria
    Posts
    22

    Question Unanswered: Problem with elementary query!

    Hi, I have one table with one field(Primary key-text field). I enter digits in this field. I want to show only the last 3 characters of each record (string) so this is the critaria I use in the query builder:
    Right([Table1]![Field1];3)

    This is the SQL code that Access generates based on my query:
    SELECT [Table1].[Field1]
    FROM Table1
    WHERE ((([Table1].[Field1])=Right([Table1]![Field1],3)));

    What is wrong with this. I have no results from this. 10x in advance.

  2. #2
    Join Date
    Mar 2004
    Location
    Estonia
    Posts
    3
    Hi

    Your query will return only records with length of field1 3 or less. When field1 is longer in all records in table1, then nothing is returned.

    I'm not sure, what do you exactly want to do, but maybe this does it:

    SELECT DISTINCT RIGHT(Field1,3) AS Field1_3 FROM Table1


    Arvi Laanemets

  3. #3
    Join Date
    Mar 2004
    Location
    Bulgaria
    Posts
    22
    10x Arvi
    The solution you gave me works just the way I want it to. Still I don't get why my query didnt. In the access help file it reads that "Right" returns the rightmost characters of a string:
    Right("abcdefg",4) returns "defg".

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Still I don't get why my query didnt.
    because you were asking for fields which had the entire field value equal to the last three characters

    WHERE ((([Table1].[Field1])=Right([Table1]![Field1],3)))

    the only fields which could possibly satisfy this criterion are fields that are exactly three characters long
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Location
    Bulgaria
    Posts
    22
    I see. 10x a lot for the help

Posting Permissions

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