Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    crosstab query and report (Access 2000 all updates)

    I am trying to do a report based on a crosstab query. SInce the column names aren't known prior to formatting, I found code (courtesty of Getz, Litwin and Gilbert in Access 2000 Developers Handbook) which will produce a report based on a crosstab query...until Parameters enter into the process.

    Below is my crosstab query SQL.
    PARAMETERS [Start Date] DateTime, [End Date] DateTime;
    TRANSFORM Count(tblVolunteerSchedule.VolDate) AS CountOfVolDate
    SELECT tblVolunteers.LastName, tblVolunteers.FirstName
    FROM tblVolunteers LEFT JOIN tblVolunteerSchedule ON tblVolunteers.VolunteerID = tblVolunteerSchedule.VolunteerID
    WHERE (((tblVolunteerSchedule.VolDate) Between [Start Date] And [End Date]))
    GROUP BY tblVolunteers.LastName, tblVolunteers.FirstName
    PIVOT tblVolunteerSchedule.VolDate;

    The crosstab query runs fine.

    The report works fine if there are no parameters. But as soon as parameters are part of the report source, the report shows completely blank.

    Below is the code for the report...
    Private Sub Report_Open(Cancel As Integer)
    Dim intColCount As Integer
    Dim intControlCount As Integer
    Dim i As Integer
    Dim strName As String

    On Error Resume Next

    Dim rst As DAO.Recordset
    Dim db As DAO.Database

    Set db = CurrentDb
    Set rst = db.OpenRecordset("Query1")


    intColCount = rst.Fields.Count
    intControlCount = Me.Detail.Controls.Count

    If intControlCount < intColCount Then
    intColCount = intControlCount
    End If

    For i = 1 To intColCount
    strName = rst.Fields(i - 1).Name
    Me.Controls("lblHeader" & i).Caption = strName
    Me.Controls("txtData" & i).ControlSource = strName
    Next i

    For i = intColCount + 1 To intControlCount
    Me.Controls("txtData" & i).Visible = False
    Me.Controls("lblHeader" & i).Visible = False
    Next i
    rst.Close
    End Sub

    I know that crosstab reports are pesky at best, but I have worked at this for so long that I don't know which way to turn to make it come out right.

    Why does the introduction of a parameter make the report go flooey? Is there a way to fix it?

    Tom

  2. #2
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query and report (Access 2000 all updates)

    The problem with opening recordsets on parameter queries is that you have to explicitly specify the parameter values for the query and then execute it.

    I'd be tempted to change your code to something like


    Private Sub Report_Open(Cancel As Integer)
    Dim intColCount As Integer
    Dim intControlCount As Integer
    Dim i As Integer
    Dim strName As String

    Dim myStr as string

    On Error Resume Next

    Dim rst As DAO.Recordset
    Dim db As DAO.Database

    mystr = "TRANSFORM Count(tblVolunteerSchedule.VolDate) AS CountOfVolDate"
    mystr = mystr & "SELECT tblVolunteers.LastName, tblVolunteers.FirstName"
    mystr = mystr & "FROM tblVolunteers LEFT JOIN tblVolunteerSchedule ON tblVolunteers.VolunteerID = tblVolunteerSchedule.VolunteerID"
    mystr = mystr & "WHERE (((tblVolunteerSchedule.VolDate) Between #"
    mystr = mystr & format([Start Date],"mm/dd/yyyy") & "# And #" & format([End Date],"mm/dd/yyyy") & "#))"
    mystr = mystr & "GROUP BY tblVolunteers.LastName, tblVolunteers.FirstName"
    mystr = mystr & "PIVOT tblVolunteerSchedule.VolDate;"


    Set db = CurrentDb
    Set rst = db.OpenRecordset(mystr)

    This will generate the query for you. You will have to replace the [StartDate] and [EndDate] with valid fields or values.

    otherwise have a look in the help for using the querydef object

    eg

    Set rst = query1.openrecordset

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

    Re: crosstab query and report (Access 2000 all updates)

    Using parameters such as [Start Date] require the user to enter a value interactively. It is difficult to get at this value programmatically. You should create a form with text boxes in which the user can enter the start date and end date, you can then use the values of these text boxes in your code. See for example <post#=248210>post 248210</post#> or <post#=155586>post 155586</post#> (both are in Access 97 format, but will work after converting to Access 2000 format)

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query and report (Access 2000 all updates)

    Stewart
    The report still showed up blank. But thanks for sending a possible solution.

    Hans
    I downloaded the first post you suggested, and will have a look at it.

    Thanks.
    Tom

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query and report (Access 2000 all updates)

    Hans
    With a little tweaking, your code worked wonderfully. I wanted to "sum" some of the columns, so had to work out some additional code to do that.

    In case anyone is interested, here is what I got to work.

    Private Sub Report_Open(Cancel As Integer)
    Dim intX As Integer
    Dim i As Integer
    Dim intColCount As Integer
    Dim intControlCount As Integer
    Dim strName As String
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim lngErr As Long

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("Query1")
    ' Set parameters for query based on values entered
    ' in frmPayDays form.
    qdf.Parameters("Forms!Form1!txtStartDate") _
    = Forms!Form1!txtStartDate
    qdf.Parameters("Forms!Form1!txtEndDate") _
    = Forms!Form1!txtEndDate

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

    intColCount = rst.Fields.Count
    intControlCount = Me.Detail.Controls.Count

    If intControlCount < intColCount Then
    intColCount = intControlCount
    End If

    For i = 1 To intColCount
    strName = rst.Fields(i - 1).Name
    Me.Controls("lblHeader" & i).Caption = strName
    If IsDate(Me.Controls("lblHeader" & i).Caption) Then
    Me.Controls("lblHeader" & i).Caption = "Present on" & vbCrLf & strName
    End If
    Me.Controls("txtData" & i).ControlSource = strName
    Next i
    For intX = 3 To intColCount
    strName = rst.Fields(intX - 1).Name
    Me.Controls("txtSum" & intX).ControlSource = "=Sum([" & strName & "])"
    Next intX

    For i = intColCount + 1 To intControlCount
    Me.Controls("txtData" & i).Visible = False
    Me.Controls("lblHeader" & i).Visible = False
    For intX = intColCount + 3 To intControlCount
    Me.Controls("txtSum" & intX).Visible = False
    Next intX
    Next i

    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

    Thanks again for your help.

    Tom

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

    Re: crosstab query and report (Access 2000 all updates)

    I'm glad it worked out; crosstab reports usually require a fair amount of tweaking.

Posting Permissions

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