week ending list

[supmktg]

I'm creating an invoice report that lists all of the transactions for the entire week. I'd like the user to be able to select a "week ending date" from a combo box on a form and then have the report's query filtered to transaction dates 'between ([week ending date]-7) and [week ending date]'. I was hoping to populate the combo box with week ending dates for prior 52 weeks from today.

Can anyone help me populate the combo box with these dates?
Is there a better way to have the user select the week for which to generate the invoice report?

Thanks,

Supmktg

 

[Nouba]

Maybe you can set up the combobox in the Load Event of the form with the following procedure.

Sub SetLast52Weeks(CboCtl As Access.ComboBox)
  Dim strRowSource            As String
  Dim i                       As Integer
  Dim dtThisWeek              As Date

  ' assuming week starts on Sunday
  dtThisWeek = Date - DatePart("w", Date, vbSunday) + 1

  For i = 0 To 51
    strRowSource = strRowSource & dtThisWeek - i * 7 & ";" & dtThisWeek + 7 - i * 7 & ";"
  Next i

  With CboCtl
    .RowSourceType = "Value List"
    .ColumnCount = 2
    .BoundColumn = 1
    .ColumnWidths = "1440;1440"
    .RowSource = left$(strRowSource, Len(strRowSource) - 1)
  End With

End Sub

 

[supmktg]

Thanks Nouba,

I changed your code just a little so that just the Week Ending Date appears in the Combo Box. Now it works exactly as I had hoped!

Supmktg

 

[namliam]

Hmz, I dont normaly use that kind off functions. I prefer query's so here goes...

Asume you have a table "myTable" with only 1 field "myNumbers"
This is filled with:
0
1
2
3
4
etc

then use this query:

SELECT Date()-Weekday(Date(),1)+1-[mynumbers]*7 AS YourDates
FROM myTable;

to get the dates you want. You might then order them as needed.....

Regards

The Mailman