Allocating Dates

[Mark A. Sam]

Hello,

I need a procedure to allocated dates withing a date range into weeks with a
starting and ending date for each week within the range. For example, if
the selected date range is 2/1/08 to 2/21/08 the beggining and ending dates
would be allocated like this:

Week Date1 Date1
1 2/1 2/2
2 2/3 2/9
3 2/10 2/16
4 2/17 2/21

I would need to create 4 records with those dates inserted into the date
fields.

In my mind it would be taking the remaning days from starting date to the
end of the first week, then allocating the dates for the remaining weeks
based on the number of days remaining. I can't work out an efficient
method.

If anyone can guide me somehow or if there is an example worked out, I'd
appreciate any help.

Thanks and God Bless,

Mark A. Sam

 

[Allen Browne]

Mark, you may be able to do this without a table, just using:
DatePart("ww", [SomeDate])
to get the week number from the date, or using:
DateAdd("ww", HowManyWeeks, FirstDateOfYear)
to get the starting date for a week from a week number.

DatePart() has soptional arguments to handle what you consider to be the
first day of the week, and which is the first week of the year.

If you want to use a lookup table of dates (e.g. where the periods cannot be
calculated), you are best to store only the first date in the period, and
then use this kind of logic to figure out the ending date and consequently
the period that any particular date belongs to:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

 

[Mark A. Sam]

Allen,

DatePart looks like a solution. Instead of using the date range, I can
number the records consequtively, beginning from the initial date. Thanks.

God Bless,

Mark

Mark, you may be able to do this without a table, just using:
DatePart("ww", [SomeDate])
to get the week number from the date, or using:
DateAdd("ww", HowManyWeeks, FirstDateOfYear)
to get the starting date for a week from a week number.

DatePart() has soptional arguments to handle what you consider to be the
first day of the week, and which is the first week of the year.

If you want to use a lookup table of dates (e.g. where the periods cannot
be calculated), you are best to store only the first date in the period,
and then use this kind of logic to figure out the ending date and
consequently the period that any particular date belongs to:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I need a procedure to allocated dates withing a date range into weeks
with a starting and ending date for each week within the range. For
example, if the selected date range is 2/1/08 to 2/21/08 the beggining
and ending dates would be allocated like this:

Week Date1 Date1
1 2/1 2/2
2 2/3 2/9
3 2/10 2/16
4 2/17 2/21

I would need to create 4 records with those dates inserted into the date
fields.

In my mind it would be taking the remaning days from starting date to the
end of the first week, then allocating the dates for the remaining weeks
based on the number of days remaining. I can't work out an efficient
method.

If anyone can guide me somehow or if there is an example worked out, I'd
appreciate any help.

Thanks and God Bless,

Mark A. Sam

 

[Mark A. Sam]

Allen, DatePart was all I needed. After a week of straining by brain it came
down to this:

'Method decides if a load stays on same line or goes to new line on report
rstTable.FindFirst "[Week] = " & DatePart("ww", rstData![Date])
If rstTable.NoMatch Then
rstTable.AddNew
rstTable![Week] = DatePart("ww", rstData![Date])
Else
rstTable.Edit
End If

Thank you and God Bless,

Mark