Date sorting

  • Thread starter Thread starter Mark Smith via AccessMonster.com
  • Start date Start date
M

Mark Smith via AccessMonster.com

I have a table that stores start dates and end dates. How can I create a
new table based on the original table that sorts start and end dates by
month?

For example, if one event covers 3 months (1/5/05 to 3/9/05), I want to
divide the event into 3 different entries, with a time span for each month
(the first one will be from 1/5/05 to 1/31/05, the second will be from
2/1/05 to 2/28/05, and the third will be from 3/1/05 to 3/9/05).

Thanks for the help.
 
Mark, there are a couple of issue to solve to get the result you want, so
this will take some effort.

The first is to generate a record for every month between the two dates.
This will involve creating a table with a record for each month. You can
then include this tblMonth in a query, select the overlapping months, and
get the monthly records. There will be no join between the two tables
(called a Cartesian product.)

The second is to identify where your records overlap with those from
tblMonth. The key idea here is that two records overlap if both these are
true:
A starts before B ends, AND
B starts before A ends.
Use that in the criteria of your Cartesian product query to identify which
events overlap with which months.

Now you have a record for every row, and the right events selected, the
third issue is to show the right dates within those months. This will
involve typing calculated fields into the Field row of the query, using
IIf().

If these are new ideas, allow yourself time to develop them one step at a
time until you get the result you need.
 
See code below. Of course, change the table and field names and empty the
receiving table.
-------------------------------

Option Compare Database
Option Explicit

Public Function Event_Parsing()

Dim SD As Date, ED As Date
Dim EDM As Integer, EDY As Integer
Dim DB As Database
Dim RstIn As Recordset, RstOut As Recordset

Set DB = CurrentDb()
Set RstIn = DB.OpenRecordset("Events with Date Range")
Set RstOut = DB.OpenRecordset("Parsed Events")

If RstIn.RecordCount > 0 Then

RstIn.MoveFirst

RecordAction:

Let SD = RstIn![Start Date]
Let ED = SD

Do While ED < RstIn![End Date]

Let EDM = IIf(Month(SD) < 12, Month(SD) + 1, 1)
Let EDY = IIf(Month(SD) < 12, Year(SD), Year(SD) + 1)

Let ED = CDate(EDM & "/01/" & EDY) - 1

RstOut.AddNew
RstOut![Event Number] = RstIn![Event Number]
RstOut![Event Name] = RstIn![Event Name]
RstOut![Start Date] = SD
RstOut![End Date] = IIf(ED <= RstIn![End Date], ED, RstIn![End Date])
RstOut.Update

Let SD = ED + 1

Loop

RstIn.MoveNext

If RstIn.EOF Then
GoTo Outahere
Else
GoTo RecordAction
End If

Else
MsgBox "No records in the Events table."
End If

Outahere:

RstIn.Close
RstOut.Close

End Function
 
Oops. Change the line "Let ED = SD" to "Let ED = SD - 1". This will
accommodate one-day events.
 
Thanks guys.

I came up with something similar and it seems to work.
 
Back
Top