Query date range

I have a report that query's how much money our business has made off of each product we have and right now you enter a date range for the week which displays the total amount for the week. I am wondering how I would go about creating a query that lists out a total for each week of the month so lets say you put in 03/01/15 - 03/31/15 is there a way that I can have that range broken down into each week of the month? Right now we have to run the report 5 different times if you want to see how much we made each week of the month.
HOTWATTAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
You can - in the report - Group by the weeknumber. However, Access is still buggy for week 53 so you may need this function:
Public Function ISO_WeekNumber( _
  ByVal datDate As Date) _
  As Byte

' Calculates and returns week number for date datDate according to the ISO 8601:1988 standard.
' 1998-2000, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.

  Const cbytFirstWeekOfAnyYear  As Byte = 1
  Const cbytLastWeekOfLeapYear  As Byte = 53
  
  Dim bytWeek                   As Byte
  Dim bytISOThursday            As Byte
  Dim datLastDayOfYear          As Date

  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
  
  If bytWeek = cbytLastWeekOfLeapYear Then
    bytISOThursday = Weekday(vbThursday, vbMonday)
    datLastDayOfYear = DateSerial(Year(datDate), 12, 31)
    If Weekday(datLastDayOfYear, vbMonday) >= bytISOThursday Then
      ' OK, week count of 53 is caused by leap year.
    Else
      ' Correct for Access97/2000 bug.
      bytWeek = cbytFirstWeekOfAnyYear
    End If
  End If
  
  ISO_WeekNumber = bytWeek

End Function

Open in new window

/gustav
0
 
Rgonzo1971Commented:
Hi,

you could add a column to your query to have the week number

Week:(DatePart("ww", [Date In]))

Regards
0
 
mbizupCommented:
If you need 'every week' regardless of whether or not there were sales in a given week, you'd have to create a "Weeks" table.

Hard to be specific with the amount of information that you've given, but the general idea is to LEFT JOIN the weeks table somehow to your query, so that all weeks appear... and then apply specific criteria limiting the dates to march.


The JOIN might look something like this

FROM
tblWeeks w LEFT JOIN tblSales s ON w.WeekNum = DatePart("ww", s.[SomeDateField])


And the WHERE clause:

WHERE s.SomeDateField BETWEEN #/3/1/2015# AND #/3/31/2015#
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.