Loop problem when time period loops from 1 to end of period again

[Lance]

My subject is a mess, i apologize...

I have a dataset which I am searching by two fields, season and week...
Season is an endless number increasing once every 16 weeks. Weeks loops
from 1-16 for each season. My goal is to write a query which will
search "backwards" x periods depending upon what I request. I have my
query running fine except for one problem: When I am searching the
beginning of a season (say from Season 2, week 2, through Season 1,
week 14), I am not getting the upper weeks of season 1. the problem is
the way my query is set up - but I am not sure how to fix it to resolve
this problem (Both fields are numbers, which might be a probem in
itself)

SELECT DISTINCT season, week
FROM incomeTable
WHERE ((season<=2) AND (week<=3))
ORDER BY incomeTable.season DESC , incomeTable.week DESC

As you can see in this example, I will get Sn 2: Wk3: through Sn 2:
Wk1, but it won't loop to Sn 1: Wk: 16 because of the way the week
criteria is set up...

I tried to combine the two, so that it would be season & week < 23, but
then 116 would be greater, and still doesn't show up. I thought about
trying to insert a zero in front of the single digit weeks, but I have
no idea how I would go about doing that. Any advice would be greatly
appreciated. (also, this query is generated in VBA given criteria from
somewhere else, so if you have an idea of how to write the query usign
VBA, that would be even better!)

Lance

 

[John Spencer]

SELECT DISTINCT season, week
FROM incomeTable
WHERE ((season<=2) AND (week<=3)) OR Season < 2
ORDER BY incomeTable.season DESC , incomeTable.week DESC

That should give you all weeks of any season less than the designated
season. So all of season 1. It should also return weeks 1, 2, and 3 of
season 2.

If you want help revising your VBA generated SQL statement, I suggest you
post the code that is currently generating the SQL. I sample of ONE way to
do this follows.

Dim strSQL as String
Dim LSeasonNum as Long, iWeekNum as Integer
LSeasonNum = 2
iWeekNum = 3

StrSQL = "SELECT DISTINCT season, week" & _
" FROM incomeTable" & _
" WHERE (season<=" & LSeasonNum & _
" AND week<=" & iWeekNum & ")" & _
" OR Season < " & LSeasonNum & _
" ORDER BY season DESC , incomeTable.week DESC"

 

[Lance]

John,

Thanks a lot. The code won't be a problem as you've presented a great
way to do it. That is basically the code I have now, just the variable
names differing. I will give this a try and post back with hopefully
success!

 

[Lance]

The query worked great. All is well now. Thanks a lot