Subreport problem with date span

  • Thread starter Thread starter Joe Cilinceon
  • Start date Start date
J

Joe Cilinceon

I have a Summary report that is grouped based on a date span. I want to add
a deposits made from the deposits subreport using the same date span. How
can this be done,or can it?
 
Do you have a little more information? When you state "grouped based on a
date span" do you actually mean filtered based on a date range? If so, how
are you applying the filter?
 
Actully I'm opening the main report based on a Between statement with the
problem being that the subreport will be based on the same date span. I hope
that is clearer. I sure do need to find a way around this as I have about 2
more subreports to add to this report that will need to work the same way. I
really haven't use filters much in a report but would that work in this
case. If you need any more please ask.
 
Sure the between statement is as below for the main Summary Report form that
calls the report:

Dim strWhere As String
strWhere = "[Payment Date]" & " Between " & Format(Me.txtStartDate,
"\#m\/d\/yyyy\#") _
& " And " & Format(Me.txtEndDate, "\#m\/d\/yyyy\#")
DoCmd.OpenReport "Summary Report", acViewPreview, , strWhere

That is working fine as far as the Main Summary report goes. The problem is
getting the subreports that use the same date span to match. The
Me.txtStartDate and Me.txtEndDate come from a Calendar Form prompt similar
to what was described on the page you listed for me to look at. I even tried
this below and it worked 1 time then stopped working.

Dim strWhere1 As String
Dim strWhere2 As String

strWhere1= "[Deposit Date]" & " Between " & Format(Me.txtStartDate,
"\#m\/d\/yyyy\#") _
& " And " & Format(Me.txtEndDate, "\#m\/d\/yyyy\#")
strWhere2 = "[Payment Date]" & " Between " & Format(Me.txtStartDate,
"\#m\/d\/yyyy\#") _
& " And " & Format(Me.txtEndDate, "\#m\/d\/yyyy\#")

DoCmd.OpenReport "Deposit Report",acViewPreview, , strWhere1, acHidden
DoCmd.OpenReport "Summary Report", acViewPreview, , strWhere2
 
You have to filter the subreport's record source. You can either change the
sql prior to opening the report or hard-code
Between Forms!frmYourForm!txtStartDate AND Forms!frmYourForm!txtEndDate

--
Duane Hookom
MS Access MVP


Joe Cilinceon said:
Sure the between statement is as below for the main Summary Report form that
calls the report:

Dim strWhere As String
strWhere = "[Payment Date]" & " Between " & Format(Me.txtStartDate,
"\#m\/d\/yyyy\#") _
& " And " & Format(Me.txtEndDate, "\#m\/d\/yyyy\#")
DoCmd.OpenReport "Summary Report", acViewPreview, , strWhere

That is working fine as far as the Main Summary report goes. The problem is
getting the subreports that use the same date span to match. The
Me.txtStartDate and Me.txtEndDate come from a Calendar Form prompt similar
to what was described on the page you listed for me to look at. I even tried
this below and it worked 1 time then stopped working.

Dim strWhere1 As String
Dim strWhere2 As String

strWhere1= "[Deposit Date]" & " Between " & Format(Me.txtStartDate,
"\#m\/d\/yyyy\#") _
& " And " & Format(Me.txtEndDate, "\#m\/d\/yyyy\#")
strWhere2 = "[Payment Date]" & " Between " & Format(Me.txtStartDate,
"\#m\/d\/yyyy\#") _
& " And " & Format(Me.txtEndDate, "\#m\/d\/yyyy\#")

DoCmd.OpenReport "Deposit Report",acViewPreview, , strWhere1, acHidden
DoCmd.OpenReport "Summary Report", acViewPreview, , strWhere2
Click to expand...
 
Hard coding would not be practical. Could you point me to some information
on setting up a filter on the subreport so that it will change with the main
report.
The main report as show earlier is based on a date span. The fields
txtStartDate/txtEndDate I could access from the subreport but it is the
exact syntax that confuses me. Thanks Duane you have been a big help. g

--

Joe Cilinceon


Duane Hookom said:
You have to filter the subreport's record source. You can either change the
sql prior to opening the report or hard-code
Between Forms!frmYourForm!txtStartDate AND Forms!frmYourForm!txtEndDate

--
Duane Hookom
MS Access MVP


Joe Cilinceon said:
Sure the between statement is as below for the main Summary Report form that
calls the report:

Dim strWhere As String
strWhere = "[Payment Date]" & " Between " & Format(Me.txtStartDate,
"\#m\/d\/yyyy\#") _
& " And " & Format(Me.txtEndDate, "\#m\/d\/yyyy\#")
DoCmd.OpenReport "Summary Report", acViewPreview, , strWhere

That is working fine as far as the Main Summary report goes. The problem is
getting the subreports that use the same date span to match. The
Me.txtStartDate and Me.txtEndDate come from a Calendar Form prompt similar
to what was described on the page you listed for me to look at. I even tried
this below and it worked 1 time then stopped working.

Dim strWhere1 As String
Dim strWhere2 As String

strWhere1= "[Deposit Date]" & " Between " & Format(Me.txtStartDate,
"\#m\/d\/yyyy\#") _
& " And " & Format(Me.txtEndDate, "\#m\/d\/yyyy\#")
strWhere2 = "[Payment Date]" & " Between " & Format(Me.txtStartDate,
"\#m\/d\/yyyy\#") _
& " And " & Format(Me.txtEndDate, "\#m\/d\/yyyy\#")

DoCmd.OpenReport "Deposit Report",acViewPreview, , strWhere1, acHidden
DoCmd.OpenReport "Summary Report", acViewPreview, , strWhere2


--
Thanks

Joe Cilinceon


Duane Hookom said:
Why don't you just state what your "between statement" is? Would that
Click to expand...
be
too
difficult?
I assume you are using a parameter prompt query. Replace the
Click to expand...
parameters
with
references to controls on a form. Check out this page
http://www.fontstuff.com/access/acctut08.htm.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top