select a range of records between two different week nums

[bluedollar]

I have the following record:

TID = primary key
week_no = integer
year = integer

What I would like to do is select a range of records which are greater than:

week_no AND year

and less than

week_no AND year

For example records that are after week number 10 year 2003 and before week number 40 year 2004.

The users of the database do not want to use the date format to this.

Is this possible?

Any help would be greatly appreciated.

Thanks

Dan

 

[BobStubbs]

I would consider having an extra field in the table, called (for example) DateKey. This would not appear on any data-entry forms etc, just in the table. Index this field.

When a user saves a record, I'd populate this field using the formula

year & week_no

Thus, week 30 in 2002 would show 200230 in this field.
In the selection process for the query or report, do the same thing, for example:
-- The user chooses a year number and a week number in separate fields or drop-down lists
-- You concatenate these to make DateKeys
-- You pass these as parameters to your query

The users will never see your DateKey field, but it will provide fast record selection for you.

I hope that this is of some use.

Bob Stubbs

 

[selavarti]

try this

SELECt....where ((week_no & year) >= (10 & 2003)) &(week_no & year) <= (10 & 2004))

 

[bluedollar]

Got it working with BobStubbs method.

Thanks

 

[PHV]

What about week 10 before week 2 ?
Instead of year & week_no I suggest 100 * year + week_no

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BobStubbs]

PHV - good point! In my example I assumed that week numbers were stored as two digits. To make sure the formula works for week numbers from 1 to 9, you should use:

year & format$(week_no, "00")

This will make sure you get values such as:

200401
200402
etc
(and not 20041, 20042)

Sorry for the oversight.


Bob Stubbs