Dynamic crosstab report results (A97/SR2)

Topic

New Reply

Need help getting the desired results for a payroll report from a dynamic crosstab query and report. Is there a way to make the totals column on the right side of the report disappear? It is a bogus quantity anyhow because it includes the total hours twice. I was able to make it disappear when there is at least one value entered in each of the columns (days). That happened when the totals is pushed over to the text box column on the far right side of the report and then making the visible property of the text box set to ‘no’. I have also made the column totals on the bottom of the report not appear by setting their text box visible property to ‘no’. The reason for that is: I could not make them mathematically correct (they are rounding to whole numbers).

I have attached a portion of my database that deals with the payroll form. If you open the ‘frmPayDays’ and enter for a Start Date: 4/13/03 and enter for an End Date: 4/20/03 and press the button for the report, it produces the desired results. The ‘Total Hours’ on the left side is the correct amount. If you enter as a Start Date: 4/21/03 and an End Date: 4/27/03, the results are not what I want. The ‘Total Hours’ on the left side of the report is still correct but I would like to display all the dates across the top of the report and make the lame “Totals” column go away.

Any help would be greatly appreciated.

Reply | Quote

Replies

Put the following statement in the Detail_Print:

Me(“col” + Format(intColumnCount + 1)).Visible = False

Change the following in the Detail_Format:
‘ Hide unused text boxes in the “Detail” section.
For intX = intColumnCount + 2 To conTotalColumns
to:
‘ Hide unused text boxes in the “Detail” section.
For intX = intColumnCount + 1 To conTotalColumns ‘ 2

Change the following statement in the PageHeader_Format:

‘ Make next available text box Totals heading.
Me(“Head” + Format(intColumnCount + 1)).Visible = “Totals”
to:

‘ Make next available text box Totals heading.
Me(“Head” + Format(intColumnCount + 1)).Visible = False

HTH
Pat

Reply | Quote

Pat has already given you some suggestions. To display all days of the week even if data for some of them are lacking, you can use the parameters from the form to fill the dates, instead of the field names from the query. In the attached modified version of your database I have changed the headers to labels (since they are static once they have been filled) and moved the code to set their captions to the Report_Open event procedur

Reply | Quote

Pat & Hans

Thank you very much! That worked great. I am constantly amazed by the plethora of genius that is available at this site.
I owe you both a .

RonM

Reply | Quote

Was doing some experimenting with entering the payroll hours and discovered something. If no person works on one day, but someone works the following day, their time is aligned with the day when nobody worked. Any ideas on how to address this situation?

Thank you.
Ron

Reply | Quote

It’s not really. When you have a crosstab query it does not plug the holes. It just puts the data in the positions of what it finds. So if you had the situation where there was data for Monday and Sunday then these columns would be together.
The problem is that you have setup fixed positions for the day descriptions in the headings.

Pat

Reply | Quote

I was trying different things and discovered if I manually go to the table (tblHrsWorked) and enter a Date and a Name for those dates that had no labor entered, the results between name/hours/date for the report are in proper alignment. It is not even necessary to enter an hours amount in the (fldWrkHours) column of the table. Is there maybe a way with programming to see if all the dates requested on the form are included in the table, and if not, insert a date and name to produce an accurate report.

Thank you.
RonM

Reply | Quote

Here is a new version. I have moved all code to the On Open event of the report. It only fills a text box in the detail section if there is a field corresponding to the date shown in the header.

Here is the code:

Private Sub Report_Open(Cancel As Integer)
‘ Create underlying recordset for report using criteria entered in
‘ frmPayDays form.
Dim intX As Integer
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim varDummy
Dim lngErr As Long
Dim dteDate As Date
On Error GoTo ErrHandler
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(“qXtbPayroll”)
‘ Set parameters for query based on values entered
‘ in frmPayDays form.
qdf.Parameters(“Forms!frmPayDays!txtMon”) _
= Forms!frmPayDays!txtMon
qdf.Parameters(“Forms!frmPayDays!txtSun”) _
= Forms!frmPayDays!txtSun

‘ Open Recordset object.
Set rst = qdf.OpenRecordset()

For intX = 1 To 2
Me(“Col” & intX).ControlSource = “=[” & rst.Fields(intX – 1).Name & “]”
Next intX

For intX = 3 To 9
dteDate = CDate(Forms!frmPayDays!txtSun) + intX – 9
Me(“Head” & intX).Caption = dteDate
On Error Resume Next
varDummy = rst.Fields(CStr(dteDate))
lngErr = Err
On Error GoTo ErrHandler
If lngErr = 0 Then
Me(“Col” & intX).ControlSource = “=Nz([” & dteDate & “],0)”
End If
Next intX

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

Reply | Quote

That’s a neat solution Hans. Designing a report “on the fly”.

A problem you could encounter is when a user selects a non-Monday as the first date on the form. You should check it’s a Monday in the AfterUpdate event of the StartDate control on the form. Also you should add 6 days to the start date and put this date into the EndDate on the form, also set the EndDate to locked so that users cannot change it.

Pat

Reply | Quote

Hans, the results are looking very good and the obvious bugs have been squashed. Pat, your suggestions make the whole thing more impressive and dynamic. Thank you again for great support!

RonM

Reply | Quote