Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2016
    Posts
    29
    Provided Answers: 1

    Answered: Access can't represent the join expression ... <= ... in Design View

    Hello again,

    I've written a Query which is giving me the above error - the Query is:

    SELECT QRY_Pre_Calc.Retirement_Date, Max(TBL_Retirement_age.Date_of_Birth)
    FROM QRY_Pre_Calc
    INNER JOIN TBL_Retirement_age
    ON TBL_Retirement_age.Date_of_Birth <= QRY_Pre_Calc.Retirement_Date
    GROUP BY QRY_Pre_Calc.Retirement_Date

    The error message goes on to say that: "One or more fields may have been deleted or renamed", "The name of one of more fields or tables specified in the join expression may be misspelled" (I'm 100% this isn't the case), and "The join may use an operator that isn't supported in Design view, such as > or <".

    The problem, I assume, is my "<=" expression, but it seems utterly bizarre that Access can't handle > or < in Design view, especially as the query ran fine in SQL and Datasheet views. I was wondering if anyone had any experience with this or suggestions on how to get around it. At the moment, every time I go back into Design View, my Query is rewritten into something completely different.

    Thanks in advance

    p.s. I'm using Access 2010 (if that's relevant)

  2. Best Answer
    Posted by weejas

    "The GUI for query design in Access indeed cannot handle any type of join other than "=". You'll have to stick to the SQL editor for creating and maintaining queries that use other joins."


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17
    The GUI for query design in Access indeed cannot handle any type of join other than "=". You'll have to stick to the SQL editor for creating and maintaining queries that use other joins.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #3
    Join Date
    Nov 2016
    Posts
    29
    Provided Answers: 1
    Wow.. that's crazy.. thanks for the heads up.

  5. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17
    I know, and you're welcome!

    (Seriously, we still use a piece of software that has the 8.3 restriction on filenames for querying an AS400, and that can handle all join types...)
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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