Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    20

    Unanswered: Using Parameters in Crosstabs

    I have a crosstab query that I need to use a parameter specified by the user in. When I hard code a parameter it works just fine but I need an input. I have set the Query|Parameter property with no luck.

    Any ideas anyone?

  2. #2
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    Hi CSWain,

    I've got another ugly reporting work around for you. Say you wanted to get records in a date range. Store the period start and end as public functions like this:

    Public Function PeriodStart(Optional Value As Variant) As Date
    '------------------------
    '|Get or set period start.|
    '------------------------
    On Error GoTo Exit_PeriodStart
    Static dtStart As Date

    If Not IsMissing(Value) Then
    If IsDate(Value) Then
    dtStart = CDate(Value)
    Else
    dtStart = 0
    End If
    End If

    'Return function value.
    PeriodStart = dtStart

    Exit_PeriodStart:
    Exit Function

    Err_PeriodStart:
    MsgBox "PeriodStart Error: " & Err.Number & ": " & Err.Description
    Resume Exit_PeriodStart
    End Function

    Public Function PeriodEnd(Optional Value As Variant) As Date
    '-----------------------
    '|Get or set period end.|
    '-----------------------
    On Error GoTo Exit_PeriodEnd
    Static dtEnd As Date

    If Not IsMissing(Value) Then
    If IsDate(Value) Then
    dtEnd = CDate(Value)
    Else
    dtEnd = 0
    End If
    End If

    'Return function value.
    PeriodEnd = dtEnd

    Exit_PeriodEnd:
    Exit Function

    Err_PeriodEnd:
    MsgBox "PeriodEnd Error: " & Err.Number & ": " & Err.Description
    Resume Exit_PeriodEnd
    End Function

    And use them in your query like this:

    TRANSFORM Count(Jobs.JobID) AS [JobCount]
    SELECT Jobs.CustomerID, Count(Jobs.JobID) AS [Total Jobs]
    FROM Jobs
    WHERE (((Jobs.Ordered)>=PeriodStart() And (Jobs.Ordered)<=PeriodEnd()))
    GROUP BY Jobs.Customer
    PIVOT Format([Ordered],"yyyy");

    You could also use Public properties get or set these variables, or store them in a local table.

    The user enters the date range in text boxes on a form...

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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