Get start and end dates?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that is grouped by date by week...and would like, instead of
having...

'Week ??'

I would like to have....

'Week of: ?/??/???? through ?/??/????'

How do I go about getting it to pull the start and end dates for each week?
 
Start and End based on what? Do you have a date field or parameter that is
used to base your calculation on?
 
Sorry for not being more specific....I am using Access 2003 and kepping
my records in a table...

Ticket# Date Truck Yards
885 1/2/06 15 80
886 1/5/06 16 95
887 1/10/06 19 70

And so on....

I have a report designed to give me a summary grouped by 'date by week'
and sorted by date and ticket number...I finally got the week to start on
monday instead of sunday...and
would like to know the code to do the following...

where 'textbox1'= begining date for that week
'textbox2'= ending date for that week


Summary for week of: [textbox1] to [textbox2]


In which the begining and ending dates are just beeing looked up in the
records for that week
 
To get the Monday of a week
DateAdd("d",-WeekDay([DateField]-1)+1,[DateField])
Add 6 to get the Sunday following the Monday.
 
what i need is for each time it starts a new week to have a line in the date
header that says.....

where 'textbox1'= begining date for that week
'textbox2'= ending date for that week


Summary for week of: [textbox1] to [textbox2]


With the date in short date format 1/16/2006
 
Create a sorting and grouping level at
=DateAdd("d",-WeekDay([DateField]-1)+1,[DateField])
Then in the header for that group level, add a text box
="Summary for week of: " &
DateAdd("d",-WeekDay([DateField]-1)+1,[DateField]) & " to " &
DateAdd("d",-WeekDay([DateField]-1)+1,[DateField])+6
 
Thank you


Duane Hookom said:
Create a sorting and grouping level at
=DateAdd("d",-WeekDay([DateField]-1)+1,[DateField])
Then in the header for that group level, add a text box
="Summary for week of: " &
DateAdd("d",-WeekDay([DateField]-1)+1,[DateField]) & " to " &
DateAdd("d",-WeekDay([DateField]-1)+1,[DateField])+6


--
Duane Hookom
MS Access MVP
--

Hurricane Clean-up said:
what i need is for each time it starts a new week to have a line in the
date
header that says.....

where 'textbox1'= begining date for that week
'textbox2'= ending date for that week


Summary for week of: [textbox1] to [textbox2]


With the date in short date format 1/16/2006
Click to expand...
Click to expand...
 
Wow! That is a sweet and elegant little code... And it's just what I was
looking for! Thanks for posting!

Jaybird

Hurricane Clean-up said:
Thank you


Duane Hookom said:
Create a sorting and grouping level at
=DateAdd("d",-WeekDay([DateField]-1)+1,[DateField])
Then in the header for that group level, add a text box
="Summary for week of: " &
DateAdd("d",-WeekDay([DateField]-1)+1,[DateField]) & " to " &
DateAdd("d",-WeekDay([DateField]-1)+1,[DateField])+6


--
Duane Hookom
MS Access MVP
--

Hurricane Clean-up said:
what i need is for each time it starts a new week to have a line in the
date
header that says.....

where 'textbox1'= begining date for that week
'textbox2'= ending date for that week


Summary for week of: [textbox1] to [textbox2]


With the date in short date format 1/16/2006
Click to expand...
Click to expand...
Click to expand...
 
Back
Top