Easy Parameter Qry

[Groundrush]

Set Start & End Dates

I have a list box that lists the 52 week numbers of the year

week 1 being 26-03-2005 to 01-04-2005
week 2 being 02-04-2005 to 08-04-2005....etc.



My aim
By choosing a week no all the records within that date will be returned


I am trying to do away with having to type the start and end date each time a search is carried out

I need to be able to click on the week no and from that the results are shown. perhaps in another list box or something

Any thoughts on how to make this sort of thing work?

thanks

 

[stubailey007]

I cant think of an easy way to do this, but if you look at the datepart("w", date) function you may be able to figure something out. This function will return the week of the year to which the date you pass it belongs.

Shout if you need more help

Stu

 

[Groundrush]

Thanks

looking at a few examples now....looks promising

 

[Rich]

How will you manage when the year changes?

 

[Groundrush]

How will you manage when the year changes?

Yes Iv'e noticed that.


This is what I've found so far that I'm using in my query
WeekNum: DatePart('ww',[StartDate],2,2)

not getting the results I need, not sure if this is what I need to do the trick.

 

[ColinEssex]

What if you need a months worth of data?

Col

 

[Groundrush]

What if you need a months worth of data?

Col

I need this for a particular reason, I can run standard reports that picks up all the data for a particular month/s or whatever is required.

All I'm trying to do in this case is simplify the way we do it at the moment.

I have created a form with lstboxes that need to filter out the data depending on which OperativeName & start & end dates are selected

I have a table that is populated with the start and end dates that the week numbers represent.

once chosen the results will be shown on another lstbox

see screeshot of the result I am looking for

thanks

 

[Rich]

Any help ?

 

[Groundrush]

Thanks Rich....

 

[Groundrush]

Any tips on how to make this sort of thing work faster on the sql server

I have created a qry with the odbc tables that I need but it's runing very slow
and thats just to get the year, it takes even longer to go through to the second stage...

cheers