Solved

Break down date range into month identifiers in ACCES 2010

Posted on 2013-12-17
3
321 Views
Last Modified: 2013-12-22
I am getting tired and desperately need help.  I have  ultimately a report that will span 18 months, can start at any time during the year so, say if the user wants to start the 18-month span on April 15 2013, then Month1 is April, Month2 is May, up to the next 18 months. However, in the data source, for a project I could have to start with is a date range say, Start Date for FTE Assignment = May 20, 2013, End Date for FTE Assignment is September 20, 2014.  I need a way to assign May as the Month2 and each month thereafter the next consecutive month number.  I have a procedure that use the report start date to determine which months are to be considered as Month1, Month2, etc to Month18, but I need to take the data start and end dates, break it down into the months as assign each month to the correct Month Report number and am at a loss.  I thought I would start with a date diff to get the count of the months between May and the end Start date, but then got stuck.  I am trying to get staffing needs for various projects over the 18-month span and the projects all start/end on different dates, so need to determine staff needs for the months.

Hope that makes sense.

Sandra
0
Comment
Question by:ssmith94015
  • 2
3 Comments
 
LVL 13

Expert Comment

by:magarity
Comment Utility
This kind of project is made a lot easier with a standalone date reference table.  Make a table with a sequence number for the primary key and then dates as days,  dates as text, a column for number in year, etc. Populate the table with years of dates and make sure they are in order so the sequence number is in order. Then you can use the sequence number to do the 18 month calculations, etc.
Sorry, I'm typing this on a phone so I can't go into much detail but hopefully you get the ideas.  Any questions I can write more tomorrow
0
 

Accepted Solution

by:
ssmith94015 earned 0 total points
Comment Utility
Actually, that really does not get to the problem is dynamically assigning the month sequence to varying start months.  I appreciate the suggestion, but I tried that and still had the same problem.  Actually, I built  function that if I pass in the FTE start date and the start date of the report, it seems to return what I need.  However, am going to post another related question on this subject, again thank you.
0
 

Author Closing Comment

by:ssmith94015
Comment Utility
Created function that actually did the needed processing.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now