Multiple crosstab queries by date

[TashiraRonin]

I have multiple cross tab queries for all of which the output results are defined by a date range. That is the cross tab recalculates based on the date range.

I am trying to generate one / or many reports and put the dates in through a form. So far I have the query structure working fine eg.
PARAMETERS [Forms]![DatesRange]![BeginDate] DateTime, [Forms]![DatesRange]![EndDate] DateTime;
TRANSFORM Format(nz(Count([ID])/[Total Of ID],0),"#,##0.0%") AS Expr1
SELECT [Application data from Mickey].[Funding Recommended or Advised], Count([Application data from Mickey].ID) AS [Total Of ID]
FROM [Application data from Mickey]
WHERE ((([Application data from Mickey].[Application Eligibility Date]) Between Forms!DatesRange!BeginDate And Forms!DatesRange!EndDate))
GROUP BY [Application data from Mickey].[Funding Recommended or Advised]
PIVOT [Application data from Mickey].[Assessment Type];


but the code for the form fails

at For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))


The whole VBA code on the report run from the form is

Option Compare Database

' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 11

' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.
lngReportTotal = 0

' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

End Sub


Private Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer
Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
Next intX

' Put row total in text box in the "Detail" section.
Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub


Private Sub Detail_Retreat()

' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious

End Sub


Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).Name
Next intX

' Make next available text box Totals heading.
Me("Head" + Format(intColumnCount + 1)) = "Totals"

' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX

End Sub


Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub


Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
rstReport.Close
Cancel = True

End Sub


Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!DatesRange
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("Form - Funding versus assessment type by % by dates")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!DatesRange!BeginDate") _
= frm!BeginDate
qdf.Parameters("Forms!DatesRange!EndDate") _
= frm!EndDate

' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub


Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer

' Put column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
For intX = 2 To intColumnCount
Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
Next intX

' Put grand total in text box in report footer.
Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX

End Sub


Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub

I have multiple crosstab queries so I am wondering if that means when the code on the form works I need create multiple buttons to call each report?

Does that mean I format multiple reports as well?

for the most part I used

http://support.microsoft.com/kb/328320.

to guide my work plus I have recieved some code help from persons on EE but know I am well and truly baffled and don't know enough coding.
All answers gratefully recieved

 

[dhookom]

Can you step back from all the code and existing solution to tell us something about your tables and fields and how you want them displayed in your report?

IMHO, the solution you have found is a rather poor one. I expect we could find a better one if we knew what you were attempting to do.

Duane
Hook'D on Access
MS Access MVP

 

[TashiraRonin]

Ok No worries and THANK YOU

I am trying to produce a number of reports from a tables which largely contain dates

The reports are counts and percentages in both cross tabs and straight queries

I am hoping to use a form fo the user to select the date range and then either buttons, or better a drop down list to choose the report the would like outputted.

The all the report calculations need to reclaculate based on the date range.

I can supply screen shots or the db.

 

[dhookom]

You haven't given us much to work with regarding your tables, fields, records, specs,...

The is an FAQ in this forum that describes how to create reports with column headings of date intervals. faq703-5466


Duane
Hook'D on Access
MS Access MVP

 

[TashiraRonin]

ok back to basics

I have several crosstab queries which recalculate based on a date range eg
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Format(nz(Count([ID])/[Total Of ID],0),"#,##0.0%") AS Expr1
SELECT [Application Title and Key].[Assessment Type], Count([Application Title and Key].ID) AS [Total Of ID]
FROM [Application Title and Key]
WHERE ((([Application Title and Key].[Application Recieved Date]) Between [Enter Start Date] And [Enter End date]))
GROUP BY [Application Title and Key].[Assessment Type]
PIVOT [Application Title and Key].[Funding Recomendation or Advised];

This data is all coming from a single table.

Which require date range and recalculate the out put for each different date range

I cannot use the report wizard to generate the report.

What is the best way to have the date range put in by the user such that it can generate a report which also displays the date range that was chosen.

I can get both a standard crosstab to work and I understand name a text field so that it call from the form for the chosen date range. As I have done this for straight/ simple queries.

I am stuck at the interface between the form and the output of the calculated crosstab onto a form. Hence the attempt at using VBA.

I was not sure if there was a problem with the SQL of the query itself ?

Or if I am just thinking about achieving this the wrong way. If the dynamic header process is the best way ..OKthen I will try and get my head around it, but the number of [assessment types] or [Funding Recomendation or Advised] or any otherof the crosstabs I am doing are a fixed number of choices and won’t be changing

Hope this helps maket he question clearer
And I APPRECIATE the support given

 

[TashiraRonin]

The table has the Fields

ID (this is the primary key)
Assessment_Assigned_Number
Assessment Type
Date_of_Application
Funding Recomendation or Advised
Etc

 

[TashiraRonin]

OK I went back to my orginal code before using the form process to check it still worked now that I have moved to the real database
and now it won't work it says
Undefined funtion "format" in expression. Maybe this is the cause of the form to report process failing

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Format(nz(Count(ID)/[Total Of ID],0),"#,##0.0%") AS Expr1
SELECT [Application Title and Key].[Assessment Type], Count([Application Title and Key].ID) AS [Total Of ID]
FROM [Application Title and Key]
WHERE ((([Application Title and Key].[Application Recieved Date]) Between [Enter Start Date] And [Enter End date]))
GROUP BY [Application Title and Key].[Assessment Type]
PIVOT [Application Title and Key].[Funding Recomendation or Advised];

 

[dhookom]

If Format() doesn't work, you probably have a missing reference. Try Doug Steele's page at

http://www.accessmvp.com/DJSteele/AccessReferenceErrors.html.

Also, IMHO, don't ever use parameter queries faq701-6763. You can reference the controls on the forms from within your report with text box control sources like:

=Forms!frmRptCriteria!txtStart

If your column headings won't change then you can etner the column headings into the Column Headings proeprty of the crosstab query.

Duane
Hook'D on Access
MS Access MVP