Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic crosstab report results (A97/SR2)

    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.
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Dynamic crosstab report results (A97/SR2)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic crosstab report results (A97/SR2)

    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
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic crosstab report results (A97/SR2)

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> .

    RonM

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic crosstab report results (A97/SR2)

    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

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Dynamic crosstab report results (A97/SR2)

    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

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic crosstab report results (A97/SR2)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic crosstab report results (A97/SR2)

    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
    Attached Files Attached Files

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Dynamic crosstab report results (A97/SR2)

    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

  10. #10
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic crosstab report results (A97/SR2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •