Date Range Query

[awstewar]

I have a table with sales data and a date associated with each entry. I want to create a query that will prompt me for a date range and then group the data by week. I am able to get the data to display for each day or grouped for the period, but I am unable to get it to group in 7 day week chunks. If anyone has any ideas, please help.

Thanks!

 

[raskew]

Hi -

Try copying / pasting the following function to a new module in your application.

Then place this in the criteria cell of the date in question:

Between getstartdate([enter a date]) And getstartdate([enter a date]) + 6

I've set the default week start date as 2 (Monday). You could change to any

day of the week by calling it like this (example: Wednesday (4))

Between getstartdate([enter a date], 4) And getstartdate([enter a date], 4) + 6

... or by modifying the default in the code.

Run the query and, when prompted, enter any date within the target week.

Function GetStartDate(dteMyDate As Date, _
                      Optional pDay As Integer = 2) As Date
'*******************************************
'Purpose:       Return the first day of a week
'               based on a date input by user
'               and a starting weekday (pDay)
'Coded by:      raskew
'Parameters:    dteMyDate: the target date
'               pDay: Day of week of starting day [Sun(1) - Sat(7)]
'Inputs:        from the debug window:
'               ? GetStartDate(#2/22/03#, 4)
'Output:        2/19/03
'*******************************************
Dim StartDate As Date

    pDay = IIf(IsMissing(pDay), 2, pDay)
    If WeekDay(dteMyDate) = pDay Then
       StartDate = dteMyDate
    Else
       StartDate = dteMyDate - (WeekDay(dteMyDate) + _
                   IIf(WeekDay(dteMyDate) <= pDay, 7, 0) - pDay)
    End If
    
    GetStartDate = StartDate
    
End Function

HTH - Bob

 

[awstewar]

Helpful

That was helpful, but I would like to be able to enter in a date range of a month and have the query group together each week in the date range selected.

 

[raskew]

...I would like to be able to enter in a date range of a month...

Please provide an example of what you're trying to do.

Bob

 

[awstewar]

explanation

I have a table of a years worth of data.

date,sales data
1/1/04 , 2
1/2/04 , 10
1/3/04 , 6
.
.
.
7/1/04 , 9

I would like to enter in a date range. Ex) 1/1/04 - 2/1/04
and have the query print out the sum of the sales data by week.
ex) week 1 , 30
week 2 , 50