Using Report Header (Form control) as Variable in Report Expression 1

[JimStrand]

I have a text Box on a report with following calculation. [26] represents Week 26 on a Union Qry of crosstab queries which is the data source for the report.

=Sum((IIf([ModeName]="Bus Transportation",IIf([CYBudPY]="CY",(([26])),0),Null)))

This works fine.

In the Header of the Report I have another text box with a reference to the largest week in the datasource. This is week 26

=[Forms]![checkmax]![WeekNumber]
=26

On the report header the 26 appears.

What I want to do is pass this form reference to the report calculation:

Something in the line of:

=Sum((IIf([ModeName]="Bus Transportation",IIf([CYBudPY]="CY",(([Forms]![checkmax]![WeekNumber])),0),Null)))

this syntax doesn't work but I'm hoping someone can help with proper syntax or method to reference the Form Control as this variiable changes every week.

Thank you in advance.

Jim

 

[dhookom]

Can you un-crosstab the query so that the ‘26’ becomes a value in the column that had been the heading?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JimStrand]

HI Duane - thank you for your response. UMD Bulldog Alum appreciates your Northern support!. Unfortunately I cannot un-crosstab the query as I have 4 crosstab queries in a union query which is the data source for the report. The 26 represents the current final column in data set and crosstab query(current week). I need to refer also to weeks 25,24,23 and average of the last weeks in report also. Any other ideas would be very helpful.

 

[JimStrand]

Hi Duane, I'm redesigning the solution as you've suggested. Convert all crosstab queries into select queries, union them, and then change the report reference. I will let you know if this resolves my issue. Thank you.

 

[dhookom]

Good luck. I'm anxious to hear back on how it works out.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JimStrand]

OK Now I've converted all queries to select queries and now I am having difficulty calculating a moving YTD Average for the Amount. Week 2 for example is taking just the week 2 amount and dividing by 2 when I need the Amounts of week 1 and2 to be included in the YTD Average. Any suggestions on modifying YTD AVE?

SELECT Mode.ModeName, CalendarPayroll_CY.Week_Num, "CY" AS CYBudPY, Sum(Weekly_Overtime_Tbl.EXPN_AMT) AS Amount, (Sum([EXPN_AMT])/Max([Weekly_Overtime_Tbl.Week])) AS [YTD AVE]
FROM CalendarPayroll_CY INNER JOIN (Mode INNER JOIN Weekly_Overtime_Tbl ON Mode.ModeName = Weekly_Overtime_Tbl.[Report Description]) ON CalendarPayroll_CY.Description = Weekly_Overtime_Tbl.INVOICE_DESCR
GROUP BY Mode.ModeName, CalendarPayroll_CY.Week_Num, "CY", Mode.ReportSort
ORDER BY Mode.ReportSort;

 

[dhookom]

I think this is what you are after where the query calculates the average LinePrice by customer for the YTD:

SELECT qselOrderDetailAmounts.CustomerName, qselOrderDetailAmounts.OrderDate, 
  qselOrderDetailAmounts.LinePrice,
 (SELECT AVG(LinePrice)
  FROM qselOrderDetailAmounts B
  WHERE B.CustomerName = qselOrderDetailAmounts.CustomerName AND
   B.OrderDate <= qselOrderDetailAmounts.OrderDate AND
   Year(B.OrderDate) = Year(qselOrderDetailAmounts.OrderDate)) AS RunningSum
FROM qselOrderDetailAmounts;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JimStrand]

OK This tactic worked, and report is done. Thank you Duane! One problem I am having is with email function:

DoCmd.SendObject acSendReport, "MTA_WeeklyOvertimeRprt_ByDept.pdf", strOutputFormat, strTo, strCC, , "All Department - Weekly OT Report", "Attached is the Weekly Overtime Report for All departments.", _
False, "S:\FinMgt\Beavin_Archives\JS\Overtime Data\WeeklySummaryReports\MTA_WeeklyOvertimeRprt_ByDept.pdf"

when i run the email function driven by macro on the main form I receive following error "Cannot find obect 1"

This email function has worked for me previously so I cannor see why this is occurring. Any thoughts.


----------------------------------------------------------------------------------------------------------------------------------

Function EmailReports()

Dim strOutputFormat As String
Dim strObjectName As String

strOutputFormat = "PDF Format (*.pdf)"
strObjectName = "MTA_WeeklyOvertimeRprt_ByDept.pdf"

'strOutputFormat = "JPG Format (*.jpg)"
'strOutputFormat = "XLS Format (*.xls)"

'GENERAL SYNTAX
'DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext]
'[, editmessage][, templatefile]
'acFormatPDF

'Reference to Current Database
Dim db As Database
Set db = CurrentDb()

'Identify table with names
Dim tbl As TableDef
Set tbl = db.TableDefs("ListA")

'Declare a string variable to hold the names
Dim strTo As String
strTo = ""

Dim strname As String
strname = ""


'Declare a counter for a loop
Dim i As Integer
i = 0


'Open a recordset against the table with names
Dim rst As Recordset
Set rst = tbl.OpenRecordset

With rst

If .RecordCount > 0 Then 'Verify records even exist first!

.MoveFirst 'Moves to the first position

Do While i <= .RecordCount And Not .EOF

strname = rst!Name

strTo = strTo & ";" & strname

i = i + 1

.MoveNext

Loop

Else
'Do Nothing

End If
End With



'reset strTo without preceeding comma

strTo = Mid(strTo, 2, Len(strTo))

Debug.Print strTo



' 2nd step Begin process of building CC list-----------------------------------------------------------

Dim tbl2 As TableDef
Set tbl2 = db.TableDefs("ListB")

Dim strCC As String
strCC = ""

Dim strName2 As String
strName2 = "ListB"[URL unfurl="true"]https://res.cloudinary.com/engineering-com/image/upload/v1704301613/tips/MTA_WeeklyOvertimeRprt_ByDept_x1qafd.pdf[/url]

i = 0

'Open a recordset against the table with names
Dim rst2 As Recordset
Set rst2 = tbl2.OpenRecordset

With rst2

If .RecordCount > 0 Then 'Verify records even exist first!

.MoveFirst 'Moves to the first position


Do While i <= .RecordCount And Not .EOF

strName2 = rst2!Name

strCC = strCC & ";" & strName2



i = i + 1

.MoveNext

Loop

Else
'Do Nothing

End If
End With


'reset strTo without preceeding comma

strCC = Mid(strCC, 2, Len(strCC))

' 3rd step Begin process of building CC list------------------------------------------------------------

Dim tbl3 As TableDef
Set tbl3 = db.TableDefs("ListF")

Dim strToC As String
strToC = ""

Dim strName3 As String
strName3 = "ListF"

i = 0

'Open a recordset against the table with names
Dim rst3 As Recordset
Set rst3 = tbl3.OpenRecordset

With rst3

If .RecordCount > 0 Then 'Verify records even exist first!

.MoveFirst 'Moves to the first position


Do While i <= .RecordCount And Not .EOF

strName3 = rst3!Name

strToC = strToC & ";" & strName3

i = i + 1

.MoveNext

Loop

Else
'Do Nothing

End If
End With

'reset strTo without preceeding comma

strToC = Mid(strToC, 2, Len(strToC))


'4th step send out the 2 emails to the groups defined in the ListA, ListB, ListC tables

'original text for ListA below
'DoCmd.SendObject acSendReport, "All_Depts_Rprt", strOutputFormat, strTo, strCC, , "All Department - Weekly ID Sales ReportAB", "Attached is the ID Sales Report for All departments.", _
'False, "M:\Corp_Mkt\Non-Perishables\Reports\IdSales"

'rewrite
DoCmd.SendObject acSendReport, "MTA_WeeklyOvertimeRprt_ByDept.pdf", strOutputFormat, strTo, strCC, , "All Department - Weekly OT Report", "Attached is the Weekly Overtime Report for All departments.", _
False, "S:\FinMgt\Beavin_Archives\JS\Overtime Data\WeeklySummaryReports\MTA_WeeklyOvertimeRprt_ByDept.pdf"

Debug.Print strCC
Debug.Print strToC
End Function

 

[Andrzejek]

It would be nice to have your code formatted as CODE:

...
With rst
    If .RecordCount > 0 Then [green]'Verify records even exist first![/green]
        .MoveFirst [green]'Moves to the first position[/green]
        Do While i <= .RecordCount And Not .EOF
            strname = rst!Name
            strTo = strTo & ";" & strname
            i = i + 1
            .MoveNext
        Loop
    Else[green]
        'Do Nothing[/green]
    End If
End With
...

and point to which line of your code causes the error.

BTW - there are a lot of unnecessary, superfluous code in your example, just my opinion.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JimStrand]

Thanks Andy, The error is generated by the DoCmd statement:

DoCmd.SendObject acSendReport, "MTA_WeeklyOvertimeRprt_ByDept", strOutputFormat, strTo, strCC, , "All Department - Weekly OT Report", "Attached is the Weekly Overtime Report for All departments.", _
False, "S:\FinMgt\Beavin_Archives\JS\Overtime Data\WeeklySummaryReports\MTA_WeeklyOvertimeRprt_ByDept.pdf

 

[JimStrand]

I figured out the error. The name of the report in the database needs to match the name which had been exported and saved on the server. Thank you.

 

[dhookom]

Please mark the answer(s) with Great Post! so others will know your question has been resolved.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016