DatePrt - Week No & Year Dropdown

[Guest]

Hi all,
I have an unbound criteria form for filter sales orders processed in a week.
Currently, I have two unbound date fields on that criteria form that I
supply the start of week date and end of week date to get the sales made in
that week.

But now, I would really like to have a drop down that lists the week number
and its year so that I could just select that for the week criteria...
For example,
34 - 2006
35-2006
36-2006
then continues onto 2007

I have searched the discussion groups but couldn't find something quite Im
looking for.

Thanks for any help...

 

[pietlinden]

Hi all,
I have an unbound criteria form for filter sales orders processed in a week.
Currently, I have two unbound date fields on that criteria form that I
supply the start of week date and end of week date to get the sales made in
that week.

But now, I would really like to have a drop down that lists the week number
and its year so that I could just select that for the week criteria...
For example,
34 - 2006
35-2006
36-2006
then continues onto 2007

There are several ways of doing this...
If you create a table of years (2000-2007, for example) and a table of
week numbers (1-52). use a cartesian product to generate the individual
(year, weeknumber) combinations. then you could set the controlsource
of the combobox to that query. You could add columns to format the
data any way you want.

SELECT tblYears.YearNo, tblWeekNos.WeekNo, [YearNo] & "-" &
Format([weekno],"00") AS WeekAndYear
FROM tblYears, tblWeekNos
ORDER BY tblYears.YearNo, tblWeekNos.WeekNo;

'---code to add weeknumbers... yes, I admit it, I'm lazy..
Public Sub AddWeekNumbers()
Dim intCounter As Integer
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblWeekNos", dbOpenTable,
dbAppendOnly)
For intCounter = 1 To 52
rs.AddNew
rs.Fields(0) = intCounter
rs.Update
Next intCounter
rs.Close
Set rs = Nothing
Debug.Print intCounter & " weeks added to tblWeekNos"
End Sub

Then if you have a 3-column combobox based on the query, you can show
the third column, which would be cboMyColumn.Columns(2), and then use
the 0th and 1st columns in your filter.

 

[Guest]

Hi pietlinden,
I was thinking more or less in the way of MS Access having a built in
feature where I can easily have it as an unbound field on a form which will
only act as a criteria field instead of having to create tables and storing
data in them...

But thanks for the suggestion and I will take on it and see how it goes..
--
niuginikiwi
Nelson, New Zealand

Hi all,
I have an unbound criteria form for filter sales orders processed in a week.
Currently, I have two unbound date fields on that criteria form that I
supply the start of week date and end of week date to get the sales made in
that week.

But now, I would really like to have a drop down that lists the week number
and its year so that I could just select that for the week criteria...
For example,
34 - 2006
35-2006
36-2006
then continues onto 2007

There are several ways of doing this...
If you create a table of years (2000-2007, for example) and a table of
week numbers (1-52). use a cartesian product to generate the individual
(year, weeknumber) combinations. then you could set the controlsource
of the combobox to that query. You could add columns to format the
data any way you want.

SELECT tblYears.YearNo, tblWeekNos.WeekNo, [YearNo] & "-" &
Format([weekno],"00") AS WeekAndYear
FROM tblYears, tblWeekNos
ORDER BY tblYears.YearNo, tblWeekNos.WeekNo;

'---code to add weeknumbers... yes, I admit it, I'm lazy..
Public Sub AddWeekNumbers()
Dim intCounter As Integer
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblWeekNos", dbOpenTable,
dbAppendOnly)
For intCounter = 1 To 52
rs.AddNew
rs.Fields(0) = intCounter
rs.Update
Next intCounter
rs.Close
Set rs = Nothing
Debug.Print intCounter & " weeks added to tblWeekNos"
End Sub

Then if you have a 3-column combobox based on the query, you can show
the third column, which would be cboMyColumn.Columns(2), and then use
the 0th and 1st columns in your filter.

 

[J. Goddard]

Hi -

I foresee difficulties if you try to use "week number". A year is longer
than "52 weeks" (by a day), and thus has to have 53 week numbers. To
make it even more confusing, week 1 of one year is the same as week 53
of the previous one. Why not just use a "for week ending" date in your
combo box - if you know the week ending date, you can compute the start
date. A little VB code could sort out what to do if there is a year
boundary.

John

 

[Guest]

Thanks J
That helps too... I may not create a table of weeks and years after all as
it is something that I may have been looking for......

Just to repeat again, my original thought was to have something like for
example you visit a airline website and do your booking and you have all
these dates automatically presented in a dropdown box to select what dates
you wana fly..
If access had a way of presenting that, where we can manipulate its format
for what type of date part, eg, wk, mth, year etc...
But I will go with what J has said because its close enough...

Thanks for all the replies,
much appreciated.