Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56

    Unanswered: strange results with Between operator

    I'm using Access 2000. The member r937 has many examples of "generating" a number of rows based on a column value. Here's a link to one example:
    http://www.sitepoint.com/forums/showthread.php?t=431836

    The sql statement:

    Code:
    select yourtable.name
      from yourtable
    inner
      join integers
        on integers.i between 1 and yourtable.quantity
    order
        by yourtable.name
    will work for me in Access if I start a new query, go into SQL view, and enter the SQL string. I can switch between SQL view and worksheet view and everything seems fine. Then I can save the query with a name. But then if I try to open the query I just created, either in Open or Design mode, it will error out with this message:
    Code:
    Between operator without And in query expression 'integers.i Between 1'
    It is easy to fix by using "integers.i >=1 And...." , but I was wondering why Access fails with the "i Between 1 And quantity" expression.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    because access is broken, that's why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you remove the .i form the query, does that solve the problem?

    If not - explain to me what the heck the .i does

    - GeorgeV
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i is the column name, george

    did you read my thread on the other site?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    did you read my thread on the other site?
    Umm, yes?
    Ok, no... that makes sense now
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I wasn't able to get r937's version to work either. But if you are looking for something that works I will throw in a possibility. r937 will probably cringe and I will warn you that it won't be efficient with large numbers of records. I am not sure what you are using it for, but hopefully it is for printing small batches of labels or at least small range of values (1 to 10).

    SELECT yourtable.name
    FROM yourtable, integers
    WHERE (yourtable.quantity<>0) And (integers.i) Between 1 And (yourtable.quantity)
    ORDER BY yourtable.name;

    It will take all of the possible names and pair them up with all possibilities of integers in the integer table, then filter out the ones that don't match the criteria. (Not exactly sure of the order that Access performs these steps) But you can see with a large number of names (10,000) and a large range (1,000) you will have to match up 10,000,000 combinations then filter out the ones you want. I forget the name of this type of join(?).

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DC, your join is an inner join, written using the old-style comma-separated list of tables

    it can actually be simplified as follows:

    SELECT yourtable.name
    FROM yourtable, integers
    WHERE integers.i Between 1 And yourtable.quantity
    ORDER BY yourtable.name;

    your guess is as good as mine why access does not support the standard sql INNER JOIN syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by DCKunkle
    I forget the name of this type of join(?).
    Theta I believe. Theta joins are inequality joins (i.e. joins based on the joining columns not being equal). Greater than\ less than operators are the most common examples.

    Yes - Access is a pooper with anything but the simplest join. And derived tables too (though no one believes me....).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't think we are disagreeing here - theta joins are a special type of inner join. Rudy will correct my error(s)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no errors, just a minuscule correction...

    you can have both inner and outer theta joins

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    Like I said, it was easy to fix, even still using the Join syntax, by getting rid of the Between operator.

    SELECT yourtable.name
    FROM yourtable INNER JOIN integers
    ON yourtable.quantity >= integers.i;

    Which is really the same thing as the Between version. Just wonder why Access is flaky with the Between operator.

    Like you said, it's broken

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by a-dam
    Which is really the same thing as the Between version.
    almost, but not quite

    it will generate a row when i=0, and that's incorrect
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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