09-13-11, 06:26 #1Registered User
- Join Date
- Sep 2011
Unanswered: Calculate Days falling in a period - Purpose built function
I have a large piece of data which I "was" minipulating in Excel, using complex formulas and some vba, but as I know will be looking at various reports, I have imported them to Acess as tables... And was trying to query the table na dpull the number of days.
Unfortunately I am struggling trying to use an equivilant "long" formula that I would have used in Excel... see below
It basically counts network days that fall within a period (4 weeks), looking at the courses start and end dates. So in some cases, a course may be 5 days long, with 3 days falling in one period and 2 days in the next...
I'm guessing I can use a "Case" statement in a module, but struggle seeing how or where to add field names etc.....
Any pointers at all would be helpful...
09-13-11, 06:52 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
easiest technique is to use a calendar, you will need that ina nay even to workout what days belong to what period.
you can refine that calendar to store working days. so that you know that an employee may have been working for, say 10 consecutive days, in say 3 weeks becuase of public holidays.
for now just use a simple table which identifies what dates comprises what accounting periods in any one year.I'd rather be riding on the Tiger 800 or the Norton