Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    103

    Unanswered: Splitting into months

    I'm using Access 2010.

    I have a table that shows beginning date, end date, monthly rent for any number of years.
    Is there a way for me to split the beginning date and end date into each months? ie, beginningdate = 8/1/2012, enddate = 7/31/2013, MonthlyAmt = 100 so I need it to show August 2012 = 100, September 2012 = 100, October 2012 = 100... so on until July 2013 = 100.

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Just format the fields in your query with something like this:

    BeginDate:Format([beginningdate],"MMMM YYYY")

    Steve

  3. #3
    Join Date
    Nov 2011
    Posts
    103
    But that doesn't show the rest of the months. It just shows the first month and none that follows.
    This doesn't work but is there something like Months: Between [BeginningDate] and [Enddate] and have it show the months and somehow the matching amounts?
    Last edited by tobabygu; 07-31-12 at 10:42.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could probably do this using another table called say periods containing some dat information (say year + month or period start, say 1st of each month)
    then join to that table using appropriate date bands for the start & end date
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by tobabygu View Post
    But that doesn't show the rest of the months. It just shows the first month and none that follows.
    This doesn't work but is there something like Months: Between [BeginningDate] and [Enddate] and have it show the months and somehow the matching amounts?
    Yeah, sorry about that, I didn't read the question completely!

    You can use something like this to translate the date range into a string and tack on a rent amount.

    Code:
    Public Function ShowRentByMonth(BeginDate As Date, EndDate As Date, Rent As Integer) As String
    
    Do While (BeginDate <= EndDate)
        ShowRentByMonth = ShowRentByMonth & Format(BeginDate, "MMMM YYYY") & " = " & Rent & ", "
        BeginDate = DateAdd("m", 1, BeginDate)
    Loop
    
    ShowRentByMonth = Left(ShowRentByMonth, Len(ShowRentByMonth) - 2)
    
    End Function

    Steve

  6. #6
    Join Date
    Nov 2011
    Posts
    103
    Sorry SPS. I'm a little confused. I'm fairly new to Access and dumb.

    My data is in a table. Should I be moving the data to a report or a form so I could use the code that you provided? Is there a way to put a code into a table?

  7. #7
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    No worries...

    How you use a solution depends on what you are trying to accomplish and you really didn't clarify where you want the results.

    Start by creating a new module in the database window (doesn't matter what you call it) and pasting the above code into that. It's declared as public so you can access it from anywhere in your database.

    I have a table that shows beginning date, end date, monthly rent for any number of years.
    You can use it in a query by adding a column and putting this in the Field row:

    Code:
    Months: ShowRentByMonth([beginning date],[end date],[monthly rent])
    Breaking that down:
    Months: - Just the title to display for that column once the query runs
    ShowRentByMonth - The name of the function you saved in the module
    ([beginning date],[end date],[monthly rent]) - The field names in your table, these are the parameters that the function needs to do it's job

    When you run the query you will get a string result for every row that has a begin and end date.

    You can do something similar in a form by adding the call to the function in one of the events and passing it the expected values. Like I said at the beginning, it all depends on what you are trying to get as an end result

    WARNING:
    The function is only a basic framework, a simple proof of concept. It does not handle missing values, end dates that are before begin dates, or any other exceptions!

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •