We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
8 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Break down date range into month identifiers in ACCES 2010
Posted on 2013-12-17
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
Hope that makes sense.
Sandra
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
3 Comments
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
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
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database.
Split up all multi-value fields into single values:
Split up fields that belong to other things into separate tables:
Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string.
Specify the first argument, which is the expression to be returned:
Specify the second argument, which …
Suggested Courses
Course of the Month8 days, 9 hours left to enroll
738 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.