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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

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

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

Regards
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.