Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# group by week access

Posted on 2011-03-23
Medium Priority
362 Views
Hello experts.

I have grouping by week in my report.
I need show in group header date range for each week instead number of week what I have right now.

Existing formula (showing number of week in year):
=formula\$[(Date Worked)],"ww",0,0)

I' looking something like that instead:
Running report for march 2011
week 03-01-2011 thru 03-05-2011
detalis......
week ......etc
how to do that
thx
0
Question by:henryk123
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 4
• 2

LVL 74

Expert Comment

ID: 35199150
Yeah, I did this once in a previous Q....

I'll dig it up tonight if another Expert does not chime in...

JeffCoachman
0

LVL 48

Expert Comment

ID: 35199491
I have a table (tbl_Numbers) that I use for this purpose.  It contains a single field (intNumber) and 10 records (the values 0 through 9).  From this table, I create a query (qyr_Numbers) which generates numbers from 0 through 99 (easy to expand to 999 or larger) that looks like:

SELECT Tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

from qry_Numbers, you can then create another query which gives you the week start and end dates for a given 100 day period (given the start date).

SELECT DatePart("ww",DateAdd("d",[intNumber],[StartDate])) AS RangeWeek,
FROM qry_Numbers
0

LVL 74

Expert Comment

ID: 35199600
Thanks fyed, mine was much more convoluted than yours...

;-)

Jeff
0

LVL 74

Expert Comment

ID: 35199633
henryk123,

Just keep in mind that anytime you do things "Weekly", you need to take into account the year.

For example, if your data spans multiple years, you could have week 12 for *all* years, in the same Group.

Jeff
0

LVL 74

Expert Comment

ID: 35199640
...so your primary grouping should be the Year, then the week...
0

LVL 44

Expert Comment

ID: 35199657
Can you confirm you want to do this for a given month for data to date in the current year?
0

LVL 48

Accepted Solution

Dale Fye earned 2000 total points
ID: 35199862
If you modify my query slightly,  you could limit it to a particular week.  It would look something like:

PARAMETERS [StartDate] DateTime, [MyWeek] Short;
SELECT DatePart("ww",DateAdd("d",[intNumber],[StartDate])) AS RangeWeek,
FROM qry_Numbers

You could then use this query (call it qry_Dates) and create a function that would wrap that up and return the string for the header group header.

Public Function fnWeekStartAndEndDates(MyYear as integer, MyMonth as integer, MyWeek as integer) as String

Dim dtStartDate as Date
Dim qdf as dao.querydef
Dim rs as DAO.Recordset

dtStartDate = dateserial(MyYear, MyMonth, 1)
set qdf = currentdb.querydefs(qry_Dates)
qdf.parameters(0) = dtStartDate
qdf.parameters(1) = MyWeek

set rs = qdf.openrecordset
if rs.eof then
fnWeekStartAndEndDates = "Invalid combination of Year, Month, and week!"
Else
fnWeekStartAndEndDates = "Report running for " & format(dtStartDate, "mmmm yyyy") & " week " _
& Format(rs(1), "mm-dd-yyyy") & " thru " & Format(rs(2), "mm-dd-yyyy")
End If

rs.close
set rs = nothing
set qdf = nothing

End Function

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
###### Suggested Courses
Course of the Month8 days, 12 hours left to enroll

#### 721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.