Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Greater than and less than

    I am trying to apply an sql connection to retrieve data complying to a set of parameters as applied against the 2 fields
    One field is period and the other field is session

    The table contains many rows each with a period and session value as integer

    Trying the following doesn't seem to work, but not sure where it is going wrong, a pointer in the right direction would be great thanks

    SELECT
    YearS
    ,Period
    ,Session
    ,Order_Ref
    FROM OrderDtes with(NoLock)
    WHERE
    YearS='2014' and (Period>='7' and Session>='4') and (Period<='8' and Session<='1')


    The inclusion of the Session>=4 seems to remove any sessions less than 4....and the session<=1 removes those sessions greater than 1...so effectively no sessions are returned

    I have tried various bracketing but to no avail.

    TIA
    Alan

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alan,

    Using all AND operators all parts of the WHERE clause must return TRUE. There is no possible way for both the tests in parens to be true for the same record. Can you tell us in words exactly which records you are trying to select from your DB? An example of some records would be helpful, include some that would be selected and some that would not indicating which you want. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks RG
    partial table for illustrative purposes is:
    Order_Ref YearS Period Session
    W15 2014 7 1
    W16 2014 7 1
    W17 2014 7 1
    W18 2014 7 1
    W19 2014 7 4
    W20 2014 7 4
    W21 2014 7 4
    W22 2014 7 4
    W23 2014 7 4
    W24 2014 7 4
    W25 2014 8 1

    Using the parameters of greater than/= period 7 session 1 and less than/= period 8 session 1
    should return all lines of data, which it does
    But if I want to see only those records where the parameters are greater than/= period 7 session 4 and less than/= period 8 session 1
    I get nil returns when actually I am expecting to see Order_refs W19 to W25 incl.

    Hope that helps.
    I am considering using math to make the period 7 session 1 into say 71 and use that ?
    thanks
    Alan

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alan,

    Using math is a very good idea and the only way I think you'll solve this problem. Here's my take on that approach.
    SQL:
    Code:
    SELECT aseOrders.Order_Ref, aseOrders.YearS, aseOrders.Period, aseOrders.Session
    FROM aseOrders
    WHERE (((aseOrders.YearS)=2014) AND ((([Period]*1000)+[Session])>=7004 And (([Period]*1000)+[Session])<=8001));
    Access Query Design:
    aseQDJPG.JPG
    Results:
    aseResults.JPG
    Note: The results show the calculated field for reference only as you'll note both the SQL and Query Design omit that field!

    Note2: I multiplied the Period by 1000 before adding in the Session to avoid any rollover results since I didn't know how many Periods or Sessions you had.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Alan,

    Using math is a very good idea and the only way I think you'll solve this problem. Here's my take on that approach.
    SQL:
    Code:
    SELECT aseOrders.Order_Ref, aseOrders.YearS, aseOrders.Period, aseOrders.Session
    FROM aseOrders
    WHERE (((aseOrders.YearS)=2014) AND ((([Period]*1000)+[Session])>=7004 And (([Period]*1000)+[Session])<=8001));
    Access Query Design:
    aseQDJPG.JPG
    Results:
    aseResults.JPG
    Note: The results show the calculated field for reference only as you'll note both the SQL and Query Design omit that field!

    Note2: I multiplied the Period by 1000 before adding in the Session to avoid any rollover results since I didn't know how many Periods or Sessions you had.

    HTH
    that certainly helps a lot thanks! Very much appreciated

  6. #6
    2 Star Lounger
    Join Date
    Aug 2014
    Posts
    111
    Thanks
    0
    Thanked 2 Times in 2 Posts
    The problem is that you are anding all the items.

    YearS='2014' and (Period>='7' and Session>='4') and (Period<='8' and Session<='1')

    What this is saying is that to be selected a record must match ALL the tests. As one of the tests says "Session>=4" and one says "Session<=1" no record could ever match.

    My best guess as to what you want would be the following.

    YearS='2014' and (Period='7' and Session>='4') OR (Period='8' and Session<='1')

    Regards
    Gordon

Posting Permissions

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