Going Crazy Need help with between date calculation and report

[simpsondm]

Going Crazy. Please help new to access.
I have a table that stores records of proposed network-Operational changes and we have meetings every Monday and every Thursday to discuss these changes.

What I’m having a problem with – need help with is I need to be able to print a report from this table that is out lined like this.

First Report or top of report should show everything proposed in the current week (M-Su) and everything not closed to this point (using checkbox to reflect closed state). Then I need another report or bottom part of this report to show everything proposed for the next 2 weeks. All records have a submitted date, which is what I need to run these reports against.

Con someone Please help me figure this out.

 

[FancyPrairie]

At first glance, why not use a subreport to show the data for the next 2 week report.

 

[simpsondm]

Thank you for you response. Far as a main and subreport that is not a problem. The problem I'm having is getting code or query to work that will just give me these reports.

 

[jlitondo]

Hopefully this should get you started. You could have to separate command buttons on a report menu type form to open up the reports separately.
To use this, do not set any filters on your reports.Let them pull all the records. The first button could open the current week report with code like so...

Dim dtBegWeek, dtEndWeek as date
Dim strlinkcriteria As String

dtEndWeek = DateAdd("d", (7 - weekday(Date)), Date)
dtBegWeek = DateAdd("d", -6, dt_EndWeek)

strlinkcriteria = "[DATE] BETWEEN " & "#" & dtBegWeek & "#" & " And " & "#" & dtEndWeek & "#"

stDocName = "currentWeekReportName"
DoCmd.OpenReport stDocName, acPreview, , strlinkcriteria


2 - the button to your future 2 weeks report could filter out the report like so...

Dim dtBegWeek2, dtEndWeek2 As Date
Dim strlinkcriteria As String
'
'add the date capture codes from above here
'
dtBegWeek2 = DateAdd("d", 1, dt_EndWeek)
dtEndWeek2 = DateAdd("d", 14, dt_EndWeek)

stDocName = "next2WeekReportName"

strlinkcriteria = "[DATE] BETWEEN " & "#" & dtBegWeek2 & "#" & " And " & "#" & dtEndWeek2 & "#"

DoCmd.OpenReport stDocName, acPreview, , strlinkcriteria

 

[jlitondo]

Just an afterthought. You may just use one report for either selection and not have to worry about a subreport.
You could design your open-report-form this way: place an option group with two buttons, labeled "Current week" and "Next 2 week".
Place a command button labeled "Preview Report" on you form. In this button, add the logic (including above code) to determine which radio button has been selected so you know what filtering criteria to use to open your report.
You will need to change your report header depending on the selection made in your option group. To do so, you place an unbound text box in your report header section and set its control source:

= IIF("Forms!reportMenu!OptionGroup = 1","Current Week Report","Next 2 Week Focust&quot
as an example.

Another way you could do this would be to place an OCX calendar control on your form to get more flexibility as to the time frame that can be selected. Then have it highlight the beginning and end week dates. Good luck.