Greater than and less than

Topic

New Reply

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=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

Reply | Quote

Replies

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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:
38390-aseQDJPG
Results:
38391-aseResults
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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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:
38390-aseQDJPG
Results:
38391-aseResults
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 :cheers:

that certainly helps a lot thanks! Very much appreciated :cheers:

Reply | Quote

The problem is that you are anding all the items.

YearS=’2014′ and (Period>=’7′ and Session>=’4′) and (Period<='8' and Session=4″ and one says “Session=’4′) OR (Period=’8′ and Session<='1')

Regards
Gordon

Reply | Quote