Date grouping by week

  • Thread starter Thread starter Brian K. Sheperd
  • Start date Start date
B

Brian K. Sheperd

I have a text file with dates, employee number, and hours. I imported the
file into a database table. With the report wizard, my first grouping was
by employee number, and the second was by date. I wanted to group the date
by weeks; however they want the range to be from Mon to Sun. The access
grouping is from Sun to Sat. Is there a way to change this?

Thanks,
Brian
 
I have a text file with dates, employee number, and hours. I imported the
file into a database table. With the report wizard, my first grouping was
by employee number, and the second was by date. I wanted to group the date
by weeks; however they want the range to be from Mon to Sun. The access
grouping is from Sun to Sat. Is there a way to change this?

Thanks,
Brian
Click to expand...

You can use the DatePart() function.
it has an argument to set the first day of the week to whatever day
you want.
In code:
MyWeek =DatePart("ww",[DateField],vbMonday)

In the control source of an unbound control:
=DatePart("ww",[DateField],2)

See VBA help.
 
Thank you.

I added that code to a query, and then I was able to group my report.
Worked great.

Thanks again,
Brian



fredg said:
I have a text file with dates, employee number, and hours. I imported the
file into a database table. With the report wizard, my first grouping was
by employee number, and the second was by date. I wanted to group the date
by weeks; however they want the range to be from Mon to Sun. The access
grouping is from Sun to Sat. Is there a way to change this?

Thanks,
Brian
Click to expand...

You can use the DatePart() function.
it has an argument to set the first day of the week to whatever day
you want.
In code:
MyWeek =DatePart("ww",[DateField],vbMonday)

In the control source of an unbound control:
=DatePart("ww",[DateField],2)

See VBA help.
Click to expand...
 
Back
Top