Grouping and Totals.

[Hal]

I have a report (based on a query) which is filtered by the following
criteria in the OpenReport Method:

strLinkCriteria = "Format([Months], 'mmm yyyy') IN (" & _
Forms!frmPrintSelectMonth!txtCriteria & ")"

The report groups on "Months" with each month having one or more users with
"Personal Charges".

How can I create a total sum of each users "Personal Charges" in the report
footer - grouped on user?

- There may be one or more months in the report.
- There may be one or more users for each month.

I had originally created a subform, but the query behind the report will not
take the filter criteria into account. Here's the SQL for the subreport:

SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal Charges]
FROM rptBillingSummaryByMonth
GROUP BY rptBillingSummaryByMonth.User;

 

[Larry Linson]

Is there some reason that you can't inlcude that criteria in the SQL for the
report you want to embed in the Subreport? Or, instead of using it in the
DoCmd.OpenReport, include it in the basic SQL / Query
rptBillingSummaryByMonth, which I assume is the RecordSource for the main
Report.

Larry Linson
Microsoft Access MVP

 

[Hal]

Thanks Larry...

Is there some reason that you can't inlcude that criteria in the SQL for the
report you want to embed in the Subreport? Or, instead of using it in the
DoCmd.OpenReport, include it in the basic SQL / Query
rptBillingSummaryByMonth, which I assume is the RecordSource for the main
Report.

Here is an example of what the report looks like:

June 2003 User Name Charges

6/5/2003 User 1 14,200
6/8/2003 User 1 23,567
6/8/2003 User 2 7,345

July 2003 User Name Charges
7/3/2003 User 2 11,654
7/6/2003 User 1 16,900
7/7/2003 User 1 11,000
7/8/2003 User 1 19,876

***** This is from the subreport ********
User: Total Charges:
User 1 (Sum of charges above)
User 2 (Sum of charges above)
***********************************
The subreport is based on the query used to create the main report. It
groups the users and totals the charges.

I believe I have to use the DoCmd.OpenReport since the criteria comes from a
textbox. This way I can use more than one month in the criteria i.e. if I
wanted to report 2 months the criteria in the textbox would be: "Jun 2003",
"Jul 2003". I don't think there's another way to do this.

The data for the main reports underlying query comes from several (8) tables
and queries. The "Total Charges" in the subreport is based on calculated
fields in the
main reports underlying query.

Maybe I'm going about this the wrong way?


Thanks Hal

 

[Duane Hookom]

You can add the criteria into the subreport's record source query
WHERE Instr(Forms!frmPrintSelectMonth!txtCriteria ,Format([DateField],"mmm
yyyy")>0

 

[Hal]

Thanks Duane, however when I do this, it doesn't show any records...

Let me show you part of the code for the "print Preview" that creates the
WhereCondition for the query.
I have a form (frmPrintSelectMonth) with to listboxes - you know... a list
to select from and one that show the selected items (ctlS).
When the user clicks the "Print Preview" button the selected items are put
in the textbox (txtCriteria) - this is what is used in the strLinkCriteria.
If it was just ONE month it would'nt be a problem, since I could just put:
Forms!frmPrintSelectMonth!txtCriteria in the query's criteria, but since
the user might want to see more than one month I can't do that, hence the
need for "txtCriteria".

********** Code Begin **********************

If ctlS.ListCount > 0 Then
avarSItem = Split(ctlS.RowSource, ";")
Me!txtCriteria = """" & Join(avarSItem, """, """) & """"

strLinkCriteria = "Format([Months], 'mmm yyyy') IN (" & _
Forms!frmPrintSelectMonth!txtCriteria & _
")"

With DoCmd
.OpenReport strDocName, acViewPreview, , strLinkCriteria
.RunCommand acCmdZoom75
.Maximize
End With
' Set the report's printer to the modified printer object.
Reports(strDocName).Printer = prt
Else
MsgBox "There are no items to Print." & vbNewLine & _
"Please select at least one item, from the Available Items List.", _
vbOKOnly + vbExclamation, "No items selected"
Exit Sub
End If

Erase avarSItem

************ Code End *****************


BTW this is the SQL for the subreport:

SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal Charges]
FROM rptBillingSummaryByMonth
GROUP BY rptBillingSummaryByMonth.User;

Is this what you meant when you said to add the where statement to the
query?? See below...


SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal Charges]
FROM rptBillingSummaryByMonth
WHERE
(((InStr([Forms]![frmPrintSelectMonth]![txtCriteria],Format([Date],"mmm\,yyy
y")))>0))
GROUP BY rptBillingSummaryByMonth.User;



Hal

You can add the criteria into the subreport's record source query
WHERE Instr(Forms!frmPrintSelectMonth!txtCriteria ,Format([DateField],"mmm
yyyy")>0

--
Duane Hookom
MS Access MVP

Thanks Larry...
for
the

Here is an example of what the report looks like:

June 2003 User Name Charges

6/5/2003 User 1 14,200
6/8/2003 User 1 23,567
6/8/2003 User 2 7,345

July 2003 User Name Charges
7/3/2003 User 2 11,654
7/6/2003 User 1 16,900
7/7/2003 User 1 11,000
7/8/2003 User 1 19,876

***** This is from the subreport ********
User: Total Charges:
User 1 (Sum of charges above)
User 2 (Sum of charges above)
***********************************
The subreport is based on the query used to create the main report. It
groups the users and totals the charges.

I believe I have to use the DoCmd.OpenReport since the criteria comes

from

a
textbox. This way I can use more than one month in the criteria i.e. if I
wanted to report 2 months the criteria in the textbox would be: "Jun 2003",
"Jul 2003". I don't think there's another way to do this.

The data for the main reports underlying query comes from several (8) tables
and queries. The "Total Charges" in the subreport is based on calculated
fields in the
main reports underlying query.

Maybe I'm going about this the wrong way?


Thanks Hal

 

[Duane Hookom]

One issue is that your query behind the subform includes a "\, " in the
format() function. Your txtCriteria doesn't include this.
Do you understand what the Instr() function is attempting to accomplish? If
your text box contained "Red, Green, Blue" and your field was [Color] then
all records with color values of Red, Green or Blue would result in the
Instr() function returning greater than 0.

--
Duane Hookom
MS Access MVP

Thanks Duane, however when I do this, it doesn't show any records...

Let me show you part of the code for the "print Preview" that creates the
WhereCondition for the query.
I have a form (frmPrintSelectMonth) with to listboxes - you know... a list
to select from and one that show the selected items (ctlS).
When the user clicks the "Print Preview" button the selected items are put
in the textbox (txtCriteria) - this is what is used in the strLinkCriteria.
If it was just ONE month it would'nt be a problem, since I could just put:
Forms!frmPrintSelectMonth!txtCriteria in the query's criteria, but since
the user might want to see more than one month I can't do that, hence the
need for "txtCriteria".

********** Code Begin **********************

If ctlS.ListCount > 0 Then
avarSItem = Split(ctlS.RowSource, ";")
Me!txtCriteria = """" & Join(avarSItem, """, """) & """"

strLinkCriteria = "Format([Months], 'mmm yyyy') IN (" & _
Forms!frmPrintSelectMonth!txtCriteria & _
")"

With DoCmd
.OpenReport strDocName, acViewPreview, , strLinkCriteria
.RunCommand acCmdZoom75
.Maximize
End With
' Set the report's printer to the modified printer object.
Reports(strDocName).Printer = prt
Else
MsgBox "There are no items to Print." & vbNewLine & _
"Please select at least one item, from the Available Items List.", _
vbOKOnly + vbExclamation, "No items selected"
Exit Sub
End If

Erase avarSItem

************ Code End *****************


BTW this is the SQL for the subreport:

SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal Charges]
FROM rptBillingSummaryByMonth
GROUP BY rptBillingSummaryByMonth.User;

Is this what you meant when you said to add the where statement to the
query?? See below...


SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal Charges]
FROM rptBillingSummaryByMonth
WHERE
(((InStr([Forms]![frmPrintSelectMonth]![txtCriteria],Format([Date],"mmm\,yyy
y")))>0))
GROUP BY rptBillingSummaryByMonth.User;



Hal

You can add the criteria into the subreport's record source query
WHERE Instr(Forms!frmPrintSelectMonth!txtCriteria ,Format([DateField],"mmm
yyyy")>0

--
Duane Hookom
MS Access MVP

Thanks Larry...

Is there some reason that you can't inlcude that criteria in the SQL for
the
report you want to embed in the Subreport? Or, instead of using it

in
the

DoCmd.OpenReport, include it in the basic SQL / Query
rptBillingSummaryByMonth, which I assume is the RecordSource for the main
Report.

Here is an example of what the report looks like:

June 2003 User Name Charges

6/5/2003 User 1 14,200
6/8/2003 User 1 23,567
6/8/2003 User 2 7,345

July 2003 User Name Charges
7/3/2003 User 2 11,654
7/6/2003 User 1 16,900
7/7/2003 User 1 11,000
7/8/2003 User 1 19,876

***** This is from the subreport ********
User: Total Charges:
User 1 (Sum of charges above)
User 2 (Sum of charges above)
***********************************
The subreport is based on the query used to create the main report. It
groups the users and totals the charges.

I believe I have to use the DoCmd.OpenReport since the criteria comes

from

a
textbox. This way I can use more than one month in the criteria i.e.

if

I

 

[Hal]

Duane, I do understand what Instr() is doing. However that "\" is for some
reason added automatically by access to the SQL. I remove the "\" from
either the grid or the SQL and after running the query it's back. Quite
strange...

Hal

One issue is that your query behind the subform includes a "\, " in the
format() function. Your txtCriteria doesn't include this.
Do you understand what the Instr() function is attempting to accomplish? If
your text box contained "Red, Green, Blue" and your field was [Color] then
all records with color values of Red, Green or Blue would result in the
Instr() function returning greater than 0.

--
Duane Hookom
MS Access MVP

Thanks Duane, however when I do this, it doesn't show any records...

Let me show you part of the code for the "print Preview" that creates the
WhereCondition for the query.
I have a form (frmPrintSelectMonth) with to listboxes - you know... a list
to select from and one that show the selected items (ctlS).
When the user clicks the "Print Preview" button the selected items are put
in the textbox (txtCriteria) - this is what is used in the strLinkCriteria.
If it was just ONE month it would'nt be a problem, since I could just put:
Forms!frmPrintSelectMonth!txtCriteria in the query's criteria, but since
the user might want to see more than one month I can't do that, hence the
need for "txtCriteria".

********** Code Begin **********************

If ctlS.ListCount > 0 Then
avarSItem = Split(ctlS.RowSource, ";")
Me!txtCriteria = """" & Join(avarSItem, """, """) & """"

strLinkCriteria = "Format([Months], 'mmm yyyy') IN (" & _
Forms!frmPrintSelectMonth!txtCriteria & _
")"

With DoCmd
.OpenReport strDocName, acViewPreview, , strLinkCriteria
.RunCommand acCmdZoom75
.Maximize
End With
' Set the report's printer to the modified printer object.
Reports(strDocName).Printer = prt
Else
MsgBox "There are no items to Print." & vbNewLine & _
"Please select at least one item, from the Available Items List.", _
vbOKOnly + vbExclamation, "No items selected"
Exit Sub
End If

Erase avarSItem

************ Code End *****************


BTW this is the SQL for the subreport:

SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal Charges]
FROM rptBillingSummaryByMonth
GROUP BY rptBillingSummaryByMonth.User;

Is this what you meant when you said to add the where statement to the
query?? See below...


SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal Charges]
FROM rptBillingSummaryByMonth
WHERE

(((InStr([Forms]![frmPrintSelectMonth]![txtCriteria],Format([Date],"mmm\,yyy

y")))>0))
GROUP BY rptBillingSummaryByMonth.User;



Hal

You can add the criteria into the subreport's record source query
WHERE Instr(Forms!frmPrintSelectMonth!txtCriteria ,Format([DateField],"mmm
yyyy")>0

--
Duane Hookom
MS Access MVP


Thanks Larry...

Is there some reason that you can't inlcude that criteria in the

SQL
for

the
report you want to embed in the Subreport? Or, instead of using it in
the
DoCmd.OpenReport, include it in the basic SQL / Query
rptBillingSummaryByMonth, which I assume is the RecordSource for the
main
Report.

Here is an example of what the report looks like:

June 2003 User Name Charges

6/5/2003 User 1 14,200
6/8/2003 User 1 23,567
6/8/2003 User 2 7,345

July 2003 User Name Charges
7/3/2003 User 2 11,654
7/6/2003 User 1 16,900
7/7/2003 User 1 11,000
7/8/2003 User 1 19,876

***** This is from the subreport ********
User: Total Charges:
User 1 (Sum of charges above)
User 2 (Sum of charges above)
***********************************
The subreport is based on the query used to create the main report. It
groups the users and totals the charges.

I believe I have to use the DoCmd.OpenReport since the criteria

comes
from

a
textbox. This way I can use more than one month in the criteria i.e.

if

I
wanted to report 2 months the criteria in the textbox would be: "Jun
2003",
"Jul 2003". I don't think there's another way to do this.

The data for the main reports underlying query comes from several (8)
tables
and queries. The "Total Charges" in the subreport is based on calculated
fields in the
main reports underlying query.

Maybe I'm going about this the wrong way?


Thanks Hal

 

[Duane Hookom]

I also suggested you remove the comma... If you leave the comma in, the \
will automatically appear.

--
Duane Hookom
MS Access MVP

Duane, I do understand what Instr() is doing. However that "\" is for some
reason added automatically by access to the SQL. I remove the "\" from
either the grid or the SQL and after running the query it's back. Quite
strange...

Hal

One issue is that your query behind the subform includes a "\, " in the
format() function. Your txtCriteria doesn't include this.
Do you understand what the Instr() function is attempting to accomplish? If
your text box contained "Red, Green, Blue" and your field was [Color] then
all records with color values of Red, Green or Blue would result in the
Instr() function returning greater than 0.

--
Duane Hookom
MS Access MVP

Thanks Duane, however when I do this, it doesn't show any records...

Let me show you part of the code for the "print Preview" that creates the
WhereCondition for the query.
I have a form (frmPrintSelectMonth) with to listboxes - you know...

a
list

to select from and one that show the selected items (ctlS).
When the user clicks the "Print Preview" button the selected items

are
put

in the textbox (txtCriteria) - this is what is used in the strLinkCriteria.
If it was just ONE month it would'nt be a problem, since I could just put:
Forms!frmPrintSelectMonth!txtCriteria in the query's criteria, but since
the user might want to see more than one month I can't do that, hence the
need for "txtCriteria".

********** Code Begin **********************

If ctlS.ListCount > 0 Then
avarSItem = Split(ctlS.RowSource, ";")
Me!txtCriteria = """" & Join(avarSItem, """, """) & """"

strLinkCriteria = "Format([Months], 'mmm yyyy') IN (" & _
Forms!frmPrintSelectMonth!txtCriteria & _
")"

With DoCmd
.OpenReport strDocName, acViewPreview, , strLinkCriteria
.RunCommand acCmdZoom75
.Maximize
End With
' Set the report's printer to the modified printer object.
Reports(strDocName).Printer = prt
Else
MsgBox "There are no items to Print." & vbNewLine & _
"Please select at least one item, from the Available Items List.", _
vbOKOnly + vbExclamation, "No items selected"
Exit Sub
End If

Erase avarSItem

************ Code End *****************


BTW this is the SQL for the subreport:

SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal Charges]
FROM rptBillingSummaryByMonth
GROUP BY rptBillingSummaryByMonth.User;

Is this what you meant when you said to add the where statement to the
query?? See below...


SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal Charges]
FROM rptBillingSummaryByMonth
WHERE

(((InStr([Forms]![frmPrintSelectMonth]![txtCriteria],Format([Date],"mmm\,yyy

y")))>0))
GROUP BY rptBillingSummaryByMonth.User;



Hal



You can add the criteria into the subreport's record source query
WHERE Instr(Forms!frmPrintSelectMonth!txtCriteria ,Format([DateField],"mmm
yyyy")>0

--
Duane Hookom
MS Access MVP


Thanks Larry...

Is there some reason that you can't inlcude that criteria in the SQL
for
the
report you want to embed in the Subreport? Or, instead of using

it
in

the
DoCmd.OpenReport, include it in the basic SQL / Query
rptBillingSummaryByMonth, which I assume is the RecordSource for the
main
Report.

Here is an example of what the report looks like:

June 2003 User Name Charges

6/5/2003 User 1 14,200
6/8/2003 User 1 23,567
6/8/2003 User 2 7,345

July 2003 User Name Charges
7/3/2003 User 2 11,654
7/6/2003 User 1 16,900
7/7/2003 User 1 11,000
7/8/2003 User 1 19,876

***** This is from the subreport ********
User: Total Charges:
User 1 (Sum of charges above)
User 2 (Sum of charges above)
***********************************
The subreport is based on the query used to create the main

report.

It i.e.
if

 

[Hal]

Duh... I totally overlooked that "," - NOW it works!
Thank you so much.

Regards, Hal

I also suggested you remove the comma... If you leave the comma in, the \
will automatically appear.

--
Duane Hookom
MS Access MVP

Duane, I do understand what Instr() is doing. However that "\" is for some
reason added automatically by access to the SQL. I remove the "\" from
either the grid or the SQL and after running the query it's back. Quite
strange...

Hal

One issue is that your query behind the subform includes a "\, " in the
format() function. Your txtCriteria doesn't include this.
Do you understand what the Instr() function is attempting to

accomplish?
If

your text box contained "Red, Green, Blue" and your field was [Color] then
all records with color values of Red, Green or Blue would result in the
Instr() function returning greater than 0.

--
Duane Hookom
MS Access MVP


Thanks Duane, however when I do this, it doesn't show any records...

Let me show you part of the code for the "print Preview" that

creates
the

WhereCondition for the query.
I have a form (frmPrintSelectMonth) with to listboxes - you know... a
list
to select from and one that show the selected items (ctlS).
When the user clicks the "Print Preview" button the selected items are
put
in the textbox (txtCriteria) - this is what is used in the
strLinkCriteria.
If it was just ONE month it would'nt be a problem, since I could

just
put:

Forms!frmPrintSelectMonth!txtCriteria in the query's criteria, but since
the user might want to see more than one month I can't do that,

hence
the

need for "txtCriteria".

********** Code Begin **********************

If ctlS.ListCount > 0 Then
avarSItem = Split(ctlS.RowSource, ";")
Me!txtCriteria = """" & Join(avarSItem, """, """) & """"

strLinkCriteria = "Format([Months], 'mmm yyyy') IN (" & _
Forms!frmPrintSelectMonth!txtCriteria & _
")"

With DoCmd
.OpenReport strDocName, acViewPreview, , strLinkCriteria
.RunCommand acCmdZoom75
.Maximize
End With
' Set the report's printer to the modified printer object.
Reports(strDocName).Printer = prt
Else
MsgBox "There are no items to Print." & vbNewLine & _
"Please select at least one item, from the Available Items

List.",

_

vbOKOnly + vbExclamation, "No items selected"
Exit Sub
End If

Erase avarSItem

************ Code End *****************


BTW this is the SQL for the subreport:

SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal
Charges]
FROM rptBillingSummaryByMonth
GROUP BY rptBillingSummaryByMonth.User;

Is this what you meant when you said to add the where statement to the
query?? See below...


SELECT DISTINCT rptBillingSummaryByMonth.User,
Sum(rptBillingSummaryByMonth.[Personal Charges]) AS [SumOfPersonal
Charges]
FROM rptBillingSummaryByMonth
WHERE

(((InStr([Forms]![frmPrintSelectMonth]![txtCriteria],Format([Date],"mmm\,yyy

y")))>0))
GROUP BY rptBillingSummaryByMonth.User;



Hal



You can add the criteria into the subreport's record source query
WHERE Instr(Forms!frmPrintSelectMonth!txtCriteria
,Format([DateField],"mmm
yyyy")>0

--
Duane Hookom
MS Access MVP


Thanks Larry...

Is there some reason that you can't inlcude that criteria in

the
SQL

for
the
report you want to embed in the Subreport? Or, instead of

using

it for
the report. several
(8)