Converting One Record Into Multiple Records (1 Viewer)

[bmcgree1]

Here is my problem. I have a lot of different fields that all revolve around two date fields, BeginDate and EndDate. The user enters information into both these fields along with other data. My problem is that I need the begin and endDates that they enter to be broken up by months. For example, if a user enters a begin date of 1/20/2009 and end date of 5/20/2009 access would automatically create 5 new records looking something like this (and duplicating the data in the other fields along with it):

BeginDate EndDate
1/20/2009 1/31/2009
2/1/2009 2/28/2009
3/1/2009 3/31/2009
4/1/2009 4/30/2009
5/1/2009 5/20/2009

Below is some logic that might be used to do this, I'm thinking a loop could be the answer but don't know exactly how to set it up in VBA... any help is appreciated!


Dim LngMTH as Long
'1st record BeginDate as value
LED=Dateserial(Year(BeginDate),Month(BeginDate)+1,0) ' This gives the last day of the month.
exit or goto
LngMTH=Month(BeginDate)
Do until LngMTH=Month(EndDate)+1
BeginDate=Dateserial(Year(BeginDate),LngMTH,1)
EndDate=Dateserial(Year(BeginDate),LngMTH+1,0)
LngMTH=LngMTH+1
Loop
'lastrecord
BeginDate=Dateserial(Year(BeginDate),LngMTH,1)
EndDate as given value

 

[jjturner]

I had a similar issue where I wanted to derive an intraday coverage graph by hour given someone's shift start time and end time.

These types of issues I always try to solve via query rather than VBA, if possible.

I would make 2 seed tables: list of months (1 - 12), list of years (some range suitable for your purposes - i.e., 2000 - 2010)

You would pop these 2 seed tables into your query along with your main data table, then proceed to set criteria:
MonthRecord: CDate([SeedMonth] & "/" & [SeedYear]) Between CDate(Format([BeginDate],"m/yyyy")) And CDate(Format([EndDate],"m/yyyy"))

And then you could customize the results according to your needs.

HTH,
John

 

[bmcgree1]

Great John thank you, this works to break up the months. Still leaves me with one problem that maybe you know how to solve. I need it to count the number of days that are in each of the CDate() provided months. So for example if the BeginDate is 5/20/2009 and the EndDate is 7/20/2009 it would show a different count for each month in the next query column.
I know DateDiff() is the function I want to use and got it to work for counting the total number of days between the BeginDate and EndDate but I need the count for each month. Below is the syntax I used to get the total count:

DateDiff('d',[table1].[begindate],[table1].[enddate]+1,1));

 

[jjturner]

In tblSeedMonths put a field for DaysInMonth (i.e., for Jan, DaysInMonth = 31, etc.), and if necessary, put a flag field in tblSeedYears to indicate which of them is a leap year in order to modify the number of days in Feb within the query.

Presuming your purpose is something akin to finding "billable days" per month,
you'd have to test for 3 conditions (in addition to checking for Leap Year):

1. If BeginDate occurs within the same month/year as EndDate
2. If SeedMonth/Year occurs within the same month/year as BeginDate
3. If SeedMonth/Year occurs within the same month/year as EndDate

For the sake of clarity and efficiency it would be worth putting all this into a custom function, which would be something like:

Public Function GetBillableDays(dteBegin As Date, _
            dteEnd As Date, _
            iSeedMonth As Integer, _
            iSeedYear As Integer, _
            iDaysInMonth As Integer _
            bLeapYear As Boolean) As Integer
  Dim intBillableDays As Integer
  Dim strBeginMonth As String
  Dim strEndMonth As String
  Dim strSeedMonth As String

    strBeginMonth = Format(dteBegin, "m/yyyy")
    strEndMonth = Format(dteEnd, "m/yyyy")
    strSeedMonth = CStr(iSeedMonth) & "/" & CStr(iSeedYear)

    If bLeapYear And iSeedMonth = 2 Then
        iDaysInMonth = iDaysInMonth + 1  'i.e., 29 days
    End If

    If strBeginMonth = strEndMonth Then
        intBillableDays = DateDiff("d", dteBegin, dteEnd)
    ElseIf strSeedMonth = strBeginMonth Then
        intBillableDays = iDaysInMonth - Day(dteBegin)
    ElseIf strSeedMonth = strEndMonth Then
        intBillableDays = Day(dteEnd)
    Else
        intBillableDays = iDaysInMonth
    End If

    GetBillableDays = intBillableDays

End Function

So you would call it in the calculated field of your query -
BillableDaysInMonth: GetBillableDays([BeginDate],[EndDate],[SeedMonth],[SeedYear],[DaysInMonth],[IsLeapYear])

Please bear in mind I haven't tested any of this, but I believe the methodology is sound. Also note it won't work without an [IsLeapYear] Yes/No field in your tblSeedYears.

HTH,
John

 

[bmcgree1]

John,
thank you very much for this, it's very helpful! I'm trying right now to fit the code into access and am having a little trouble. First, the compiler highlights everything you call at the beginning of the code that has a comma after it:
Public Function GetBillableDays(dteBegin As Date,
dteEnd As Date,
iSeedMonth As Integer,
iSeedYear As Integer,
iDaysInMonth As Integer
bLeapYear As Boolean) As Integer

So I took the commas off the ends of them, closed the ) on the first line and had to take off the ) and the 'As Integer' on the bottom so the complier would accept it. The complier now accepts it, but I don't know if thats right.

To recap my tables that go along with this code I created 2 tables [tblSeedYears(SeedYear, IsLeapYear)] & [tblSeedMonths(SeedMonth, DaysInMonth)]. Then in my query I put the "GetBillableDays([BeginDate],[EndDate],[SeedMonth],[SeedYear],[DaysInMonth],[IsLeapYear])" into the a new query field to create a new column.

Thank you for everything you've helped me with thus far, I'm just trying to finally implement and use it. Thanks,

Brendan

 

[jjturner]

Hi Brendan,

No - you definitely need the variables declared in the function arguments. Try putting a <space> and an underscore("_") after each comma in the function declaration. This is the syntax for "continuing" your code statement on the next line.

If that doesn't let you compile (but it must), you can simply continue the variable declarations in one long line: myVariable1 As VariableTypeX, myVariable2 As VariableTypeY, etc...) As Integer

And it looks like you've got your seed tables setup correctly.

HTH,
John

 

[bmcgree1]

Okay, I see what happened. Thank you so much John, everything works perfectly!

 

[jjturner]

You're welcome Brendan, and all the best for your project!