VBA code for my report

D

Dale G

Hi,
I would like to use a modified version of this VBA code for my report, but
I’m not sure how.
The code is from the 2007 sample Northwind db. It runs in the Quarterly
report.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strSQL As String
If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or
IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Sales Reports Dialog"
Cancel = True
Exit Sub
End If
strSQL = "TRANSFORM CCur(Nz(Sum([Sales]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"
Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]
Done:
Exit Sub
ErrorHandler:
' Resume statement will be hit when debugging
If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
Resume
Else
Cancel = True
End If
End Sub

This is what I currently have in my reports Record source.

TRANSFORM Count(tblChecks.Result) AS CountOfResult SELECT
tblCheckType.Description FROM (tblCheckPerformed INNER JOIN (tblReport INNER
JOIN tblChecks ON tblReport.ReportID = tblChecks.ReportID) ON
tblCheckPerformed.CheckPerformedID = tblChecks.CheckPerformedID) INNER JOIN
tblCheckType ON tblCheckPerformed.CheckTypeID = tblCheckType.CheckTypeID
WHERE (((tblChecks.Result)="X")) GROUP BY tblCheckType.Description,
tblCheckType.SortOrder ORDER BY tblCheckType.SortOrder PIVOT
Format([ReportDate],"mmmm") In ("September","October","November","December");

Any help is appearciated.
 
D

Douglas J. Steele

It would help a lot if you explained what problem you're having.

You've posted this multiple times already, and I did reply.
 
D

Dale G

Sorry about that, I had some sort of problem with my desk top.
When I checked my laptop today I noticed the post did go through.
I'll look for your reply.


Douglas J. Steele said:
It would help a lot if you explained what problem you're having.

You've posted this multiple times already, and I did reply.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dale G said:
Hi,
I would like to use a modified version of this VBA code for my report, but
I'm not sure how.
The code is from the 2007 sample Northwind db. It runs in the Quarterly
report.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strSQL As String
If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or
IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Sales Reports Dialog"
Cancel = True
Exit Sub
End If
strSQL = "TRANSFORM CCur(Nz(Sum([Sales]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"
Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]
Done:
Exit Sub
ErrorHandler:
' Resume statement will be hit when debugging
If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
Resume
Else
Cancel = True
End If
End Sub

This is what I currently have in my reports Record source.

TRANSFORM Count(tblChecks.Result) AS CountOfResult SELECT
tblCheckType.Description FROM (tblCheckPerformed INNER JOIN (tblReport
INNER
JOIN tblChecks ON tblReport.ReportID = tblChecks.ReportID) ON
tblCheckPerformed.CheckPerformedID = tblChecks.CheckPerformedID) INNER
JOIN
tblCheckType ON tblCheckPerformed.CheckTypeID = tblCheckType.CheckTypeID
WHERE (((tblChecks.Result)="X")) GROUP BY tblCheckType.Description,
tblCheckType.SortOrder ORDER BY tblCheckType.SortOrder PIVOT
Format([ReportDate],"mmmm") In
("September","October","November","December");

Any help is appearciated.
Click to expand...


.
Click to expand...
 
D

Dale G

Thank you, it works.
Now I'll try to figure out how to make the controls and labels work like the
sample db.

One more thing if I may. Could you please asssit me with a fuction to total
the Row counts
i.e counts for September, October, & November =


strSQL = "TRANSFORM Count(tblChecks.Result) AS CountOfResult " & _
"SELECT tblCheckType.Description FROM " & _
"(tblCheckPerformed INNER JOIN (tblReport INNER " & _
"JOIN tblChecks ON tblReport.ReportID = tblChecks.ReportID) " & _
"ON tblCheckPerformed.CheckPerformedID = " & _
"tblChecks.CheckPerformedID) INNER JOIN " & _
"tblCheckType ON tblCheckPerformed.CheckTypeID = " & _
"tblCheckType.CheckTypeID " & _
"WHERE (((tblChecks.Result)=""X"")) " & _
"GROUP BY tblCheckType.Description, " & _
"tblCheckType.SortOrder ORDER BY tblCheckType.SortOrder " & _
"PIVOT Format([ReportDate],""mmmm"") In " & _
"(""September"",""October"",""November"",""December"");"
 
D

Dale G

I found the function,
=Nz(Sum([September]),0)+Nz(Sum([October]),0)+Nz(Sum([November]),0)

Dale G said:
Thank you, it works.
Now I'll try to figure out how to make the controls and labels work like the
sample db.

One more thing if I may. Could you please asssit me with a fuction to total
the Row counts
i.e counts for September, October, & November =


strSQL = "TRANSFORM Count(tblChecks.Result) AS CountOfResult " & _
"SELECT tblCheckType.Description FROM " & _
"(tblCheckPerformed INNER JOIN (tblReport INNER " & _
"JOIN tblChecks ON tblReport.ReportID = tblChecks.ReportID) " & _
"ON tblCheckPerformed.CheckPerformedID = " & _
"tblChecks.CheckPerformedID) INNER JOIN " & _
"tblCheckType ON tblCheckPerformed.CheckTypeID = " & _
"tblCheckType.CheckTypeID " & _
"WHERE (((tblChecks.Result)=""X"")) " & _
"GROUP BY tblCheckType.Description, " & _
"tblCheckType.SortOrder ORDER BY tblCheckType.SortOrder " & _
"PIVOT Format([ReportDate],""mmmm"") In " & _
"(""September"",""October"",""November"",""December"");"



Dale G said:
Hi,
I would like to use a modified version of this VBA code for my report, but
I’m not sure how.
The code is from the 2007 sample Northwind db. It runs in the Quarterly
report.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strSQL As String
If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or
IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Sales Reports Dialog"
Cancel = True
Exit Sub
End If
strSQL = "TRANSFORM CCur(Nz(Sum([Sales]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"
Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]
Done:
Exit Sub
ErrorHandler:
' Resume statement will be hit when debugging
If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
Resume
Else
Cancel = True
End If
End Sub

This is what I currently have in my reports Record source.

TRANSFORM Count(tblChecks.Result) AS CountOfResult SELECT
tblCheckType.Description FROM (tblCheckPerformed INNER JOIN (tblReport INNER
JOIN tblChecks ON tblReport.ReportID = tblChecks.ReportID) ON
tblCheckPerformed.CheckPerformedID = tblChecks.CheckPerformedID) INNER JOIN
tblCheckType ON tblCheckPerformed.CheckTypeID = tblCheckType.CheckTypeID
WHERE (((tblChecks.Result)="X")) GROUP BY tblCheckType.Description,
tblCheckType.SortOrder ORDER BY tblCheckType.SortOrder PIVOT
Format([ReportDate],"mmmm") In ("September","October","November","December");

Any help is appearciated.
Click to expand...
Click to expand...
 
G

Guest

Dale G said:
Hi,
I would like to use a modified version of this VBA code for my report, but
I’m not sure how.
The code is from the 2007 sample Northwind db. It runs in the Quarterly
report.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strSQL As String
If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or
IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Sales Reports Dialog"
Cancel = True
Exit Sub
End If
strSQL = "TRANSFORM CCur(Nz(Sum([Sales]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"
Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]
Done:
Exit Sub
ErrorHandler:
' Resume statement will be hit when debugging
If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
Resume
Else
Cancel = True
End If
End Sub

This is what I currently have in my reports Record source.

TRANSFORM Count(tblChecks.Result) AS CountOfResult SELECT
tblCheckType.Description FROM (tblCheckPerformed INNER JOIN (tblReport
INNER
JOIN tblChecks ON tblReport.ReportID = tblChecks.ReportID) ON
tblCheckPerformed.CheckPerformedID = tblChecks.CheckPerformedID) INNER
JOIN
tblCheckType ON tblCheckPerformed.CheckTypeID = tblCheckType.CheckTypeID
WHERE (((tblChecks.Result)="X")) GROUP BY tblCheckType.Description,
tblCheckType.SortOrder ORDER BY tblCheckType.SortOrder PIVOT
Format([ReportDate],"mmmm") In
("September","October","November","December");

Any help is appearciated.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change a report to a query 1
HELP NEEDED WITH REPORT 1
Blank Report 2
Paramater box pop up even if customer selected in combo box 1
CODE HELP! 2
Application defined or object defined error on report 8
Error 3270 - Property Not Found 0
Crosstab Report w/Changing Dates 5

Top