Open report based on selection

[Guest]

I have a form set up so that you may choose to preview or just print a report.
The report that shows up or prints is based on you making a selection from
an option group (Jan, Feb, etc.). And that part works (although it is the
long way, I have a separate report for each month). Now I need to add an
additional selection (Week 1, Week 2, etc.).

I have messed with it for a while and short of setting up multiple querys
for jan week 1, jan week 2, etc. I don't know how else to get it done.

any help would be appreciated.

 

[Marshall Barton]

I have a form set up so that you may choose to preview or just print a report.
The report that shows up or prints is based on you making a selection from
an option group (Jan, Feb, etc.). And that part works (although it is the
long way, I have a separate report for each month). Now I need to add an
additional selection (Week 1, Week 2, etc.).

I have messed with it for a while and short of setting up multiple querys
for jan week 1, jan week 2, etc. I don't know how else to get it done.

You only need one report and one query. The query can be
fairly simple with no criteria for the week or month. I.e.
If you open the report directly from the database window,
the report will include all months and weeks.

The trick to what you want to do is in using a little VBA
code in the button's Click event **procedure** (not the
OnClick property). The code constructs the query's criteria
and uses that as a filter in the OpenReport method's
WhereCondition argument (see VBA Help for details).

Change the button's click event procedure to something more
like:

Dim stWhere As String

If Not IsNull(optmonth) Then
stWhere = stWhere & " And Month(datefield) = " & optmonth
End If
If Not IsNull(optweek) Then
stWhere = stWhere & " And Week(datefield) - " & _
Week(DateAdd("d", -Day(datefield), datefield) = " & _
optweek
End If
stWhere = Mid(stWhere, 6)
DoCmd.OpenReport "the report" , acviewPreview, , stWhere

Note that I seriously doubt that simple week calculation is
what you have in mind so you'll have to modify it so it does
whatever it is that you want.

 

[Guest]

I'm sorry I didn't specify earlier, and I don't know if it may change the
below.
The option group for the week gives me only a 1, 2, 3, or 4 instead of using
actual dates.

 

[Marshall Barton]

I'm sorry I didn't specify earlier, and I don't know if it may change the
below.
The option group for the week gives me only a 1, 2, 3, or 4 instead of using
actual dates.

That's what I assumed. Since we're only talking about a
query, it's easy enought to try it without the complications
of the report.

You really need to think about your definition of week. My
simple calculation uses the default definition for the week
of the year, but your idea of week may very well be
different. Note that a month can easily contain part of a
fifth week and with some definitions may have part of a
sixth week,