How to check if a month is part of a date interval?

[Hejren]

I have a series of date intervals within a year and need to count the number of months "covered" in a year.

E.g. I have the intervals Jan - April, Jan - July, Sept - Dec.

Is it possible somehow to make a query or function that will be able to return the correct number of months - in this case 11?

[NeoPa]

You don't say where/how the information is stored, and your example indicates no standard format for the string values and no year values.

Without this clearly explained it's hard to help.

[dsatino]

NeoPa is right, you don't provide enough info for a detailed response. However, if your dates are formatted as such then conceptually you'll do something like this:

Month([EndDate])-Month([StartDate])+1

[NeoPa]

If you look at the example data again you'll see that wouldn't be adequate logic. In the case of the example data it would yield 15.

[Hejren]

You’re right - my apologies. I owe you a further explanation.

The months are string representations on a form of Date/Time values - a StartDate and an EndDate, that I extract as FirstMonthOfYea r and LastMonthOfYear , (using a long and probably much too complicated logic). Year is also a string – but I haven’t used it in this calculation.

I have attempted to do the calculation on the form as

 =(DateDiff("m";Min(CDate([FirstMonthOfYear]));Max(CDate([LastMonthOfYear])))+1)*4

, but this will only work as long as there are no “gaps” in the middle.

The probably much too complicated logic for FirstMonthOfYea r:

 Forms![Start]![cmbVaelgAar] AS [Aktuelt år],
CaseSearched(Year(Ophørsdato)<[Aktuelt år],'*',Year(Startdato)<[Aktuelt år],DateSerial([Aktuelt år],1,1),Month(Startdato)=1,DateSerial([Aktuelt år],1,1),Month(Startdato)=2,DateSerial([Aktuelt år],2,1),Month(Startdato)=3,DateSerial([Aktuelt år],3,1),Month(Startdato)=4,DateSerial([Aktuelt år],4,1),Month(Startdato)=5,DateSerial([Aktuelt år],5,1),Month(Startdato)=6,DateSerial([Aktuelt år],6,1),Month(Startdato)=7,DateSerial([Aktuelt år],7,1),Month(Startdato)=8,DateSerial([Aktuelt år],8,1),Month(Startdato)=9,DateSerial([Aktuelt år],9,1),Month(Startdato)=10,DateSerial([Aktuelt år],10,1),Month(Startdato)=11,DateSerial([Aktuelt år],11,1),Month(Startdato)=12,DateSerial([Aktuelt år],12,1),'*') AS [FirstDayOfYear],
IIf([Første dag i år]='*','*',Format([Første dag i år],"mmm yyyy")) AS [FirstMonthOfYear]

I hope this is sufficient explanation. I am new to this forum and new to Access as well, so please forgive me my mistakes so far. Your willingness to help me so far is much appreciated!

[dsatino]

Ah, now I see what he was looking for. Well then we certainly need more info.

[NeoPa]

It's often difficult to express your problem clearly, so we understand.

What you need to do before continuing though, is to answer the questions in my first response (post #2). I/we need a clearer understanding of where the data is stored and what type of code will be required even to access it. Please reread that post and respond as clearly as you can to all the questions therein.

I would then suggest that you create yourself a proper account to use and post with. Anonymous posting is perfectly possible, but you can see that posts stay invisible for a while after posting as they go to the moderation queue for a moderator to approve before becoming visible. Not so much of a problem when asking a new question, but quite complicated when you're involved in a thread conversation. I saw your reply (as a moderator) but DSatino saw nothing until I approved it. Just a suggestion but I expect you'd benefit from it.

[Hejren]

You're probably right about the account. This is my first post in this forum, and I was trying a modest approach - knowing that I'm not (yet) a skilled programmer. (I feel like a mouse among dinosaurs!!) But in a daring moment I might follow your advice…

When you say 'where' data is stored, would it be sufficient to say that I'm querying on 4 Access-tables? And that I'm trying to do this calculation in a report (not a form - sorry), and that I'm not sure if this problem can be solved the sql-wise in a query or the VBA-way in the report?

The year [Aktuelt år] is retrieved from a combobox on a form, and is as such probably a string? I hope I haven't left anything out this time.

[NeoPa]

Let me see if I can simplify the question :

Where does your month data come from (Jan - April, Jan - July, Sept - Dec)? Is it stored as three separate strings in a table somewhere? Do the records have two fields, one for the start and one for the end?

If the data is from a form, then how does the data get onto the form in the first place? Such values being hard-coded would seem a very unlikely scenario.

PS. Most members are also mice in your analogy. It is only a small percentage overall that offer assistance. Membership itself doesn't indicate any knowledge at all, only the tags by someone's account name and the post-count show how involved they are. Being a proper member actually makes it easier for us to help you.

[Hejren]

Hmmm - how to explain...

I have in my table a startdate and an enddate - eg. 01/08/2009 (Date) and 30/04/2010 (Date). From that I query to find first and last day of this year, which would be 01/01/2010 and 30/04/2010. These dates I then format (still using SQL) to January 2010 and April 2010, and then these values are presented on my report.

The tricky part now is to find the "affected" months when there are more of these in a group (children in an institution). Can it be done in sql - or on the report using some sort of VBA-function?

And thanky you for your patience!

Hejren - the mouse

[Hejren]

And by the way - I now have a membership-account.

[NeoPa]

No. It seems the tricky part is still to get the basic question clarified and all my questions answered.

The fact that you have two fields per record, a start and end date, and both are of type Date, is certainly good. This doesn't explain the original string in your question though. Firstly, if they are formatted as you suggest then where does Dec come from? That's not in the format you've just provided. Also, there are three ranges in your example, which is in no way explained in your answer.

It's good news that you now have an account. It would be a good idea to use it when posting in here in future. That way I won't have to find and approve all posts you make before anyone elase can even see them.

We are making progress, but you will learn in time how important attention to detail can be. For now, allow me to lead you step-by-step.

PS. Now you have an account, and once you've used it to post in here, you will be notified of any updates to this thread by email (if you choose that option) and by seeing this thread highlighted in your Subscriptions list.

[Kirsten Østerga]

"Dec 2010" comes from formating dates thus:

IIf([FirstDayOfYear]='*','*',Format([LastDayOfYear],"mmm yyyy")) AS [LastMonthOfYear]

[FirstDayOfYear] is:

CaseSearched(Year(Enddate)<[SelectedYear],'*',Year(Startdate)<[SelectedYear],DateSerial([SelectedYear],1,1),Month(Startdate)=1,DateSerial([SelectedYear],1,1),Month(Startdate)=2,DateSerial([SelectedYear],2,1),Month(Startdate)=3,DateSerial([SelectedYear],3,1),Month(Startdate)=4,DateSerial([SelectedYear],4,1),Month(Startdate)=5,DateSerial([SelectedYear],5,1),Month(Startdate)=6,DateSerial([SelectedYear],6,1),Month(Startdate)=7,DateSerial([SelectedYear],7,1),Month(Startdate)=8,DateSerial([SelectedYear],8,1),Month(Startdate)=9,DateSerial([SelectedYear],9,1),Month(Startdate)=10,DateSerial([SelectedYear],10,1),Month(Startdate)=11,DateSerial([SelectedYear],11,1),Month(Startdate)=12,DateSerial([SelectedYear],12,1),'*') AS [FirstDayOfYear]

And LastDayOfYear in a simular way.

The SelectedYear-parameter is picked from a combobox on the form that opens the report:

Forms![Start]![cmbVaelgAar] AS [SelectedYear]

I hope this makes sense. I'm sorry that I don't know how to describe it in any other way.

The three ranges...

My tables are tblInstitution and tblBarn (children) and tblBarnInst (children in an institution). The three ranges are a mere example of the unique ranges that occurs in one of the institutions, and are periods where the kids are members of a particular project.

The tricky part here is apparently to tricky for me. Sorry to have waisted your time... :o(

[Kirsten Østerga]

Never mind then. I found a solution.

[NeoPa]

I too was away for 4 days Kirsten. Unfortunate, as we were getting more information available to work with. Still some working out to do and maybe some guesswork, but I now assume that the string as displayed in the first post was not so much a string value, but a representation of various records from one of your tables. That is why I was asking the questions, as without that understanding my answers would have made very little sense to you. I need to know how everything fits together to be able to provide a solution that will work in your situation. Unfortunately, there are often too many answers that may fit the question, but nevertheless not be suitable, due to a lack of a clear understanding of the situation.

Never mind. At least you have a solution, which is good news.