Parameters on Crosstab

[SimonPGreen]

Hi all,

Have done some research on the forum and consulted the Getz, Litwin and Gilbert bible but I can't seem to nail this one.

I have a crosstab generated thus

TRANSFORM Sum(OuterQry.NetPayment) AS SumOfNetPayment
SELECT OuterQry.strPackageName AS [Package Name]
FROM OuterQry
GROUP BY OuterQry.strPackageName
PIVOT ReturnWeekEndingDate([Date]);

The ReturnWeekEndingDate function is as follows and simply returns the week ending date (Friday) for the date presented:

Function ReturnWeekEndingDate(DateToCheck) As Date

    Dim WD As Integer
    WD = Weekday(DateToCheck)
    Select Case WD
        Case Is = 1 'Sunday
        ReturnWeekEndingDate = DateAdd("d", 5, DateToCheck)  'Used in DateAdd to add number of days
        Case Is = 2 'Monday
        ReturnWeekEndingDate = DateAdd("d", 4, DateToCheck) 'WeekDayValue = 4
        Case Is = 3 'Tuesday
        ReturnWeekEndingDate = DateAdd("d", 3, DateToCheck) 'WeekDayValue = 3
        Case Is = 4 'Wednesday
        ReturnWeekEndingDate = DateAdd("d", 2, DateToCheck) 'WeekDayValue = 2
        Case Is = 5 'Thursday
        ReturnWeekEndingDate = DateAdd("d", 1, DateToCheck) 'WeekDayValue = 1
        Case Is = 6 'Friday
        ReturnWeekEndingDate = DateAdd("d", 0, DateToCheck) 'WeekDayValue = 0
        Case Is = 7 'Saturday
        ReturnWeekEndingDate = DateAdd("d", 6, DateToCheck) 'WeekDayValue = 6
    End Select
    
End Function

I would like to be able to add parameters for start and end dates for the crosstab so that I can limit the query but I simply cannot work our how to do this or if it's possible.

Any ideas?

Regards,

Simon

 

[SimonPGreen]

Sorry guys finally worked it out.

Simon