How to report on inclusive dates

[Brianwarnock]

Would sombody please explain what Pari wants in simple terms, A few posts ago he agreed that he wanted to count the inclusive number of months involving the 2 dates.
IE he agreed that from 29 Nov to 3 Dec would be 2 ?

He has previously said that he realised that using Between was wrong and he must use Datediff, now he wants to use Between.????

A very confused Brian

 

[wilpeter]

"A very confused Brian":
Pari has yet to chime in, but he hasn't indicated he's changing his objective. He's familiar with Between and I think he's wondering why there isn't a function that calculates the way we think: i.e., How many months Between January and March?
So I think he's trying to find a formula that squeezes out DateDiff and uses Between. Like, Count "mm" Between #IssueDate# And #ExpiryDate# .
In case you're reading this, Pari, don't bother trying it...I just made it up.

 

[Pari]

Dear all,

I think I have created an unnecessary stir here, which was not my intention. So firstly apoligies for any confusion on my part.

Actually the last post sumerises what I am after. I am oviously getting a little confused as to how I go about acheiving this.

Also the reason I have not been online consistentely is that as part of my work I am requried to shift between sites etc.

Yes, I am very curious as to why I would actually have to use the +1 option when I just though I could have used some form of command instead. Also there is no time field in my data.

The query is needed to help me establish how many permits have been issued over a 5 year period. We currently have 3, 6, and 12 month periods, so I would like to group by the permit types.

When we issue a permit, we add the start date but the computer system calculated the end date, which when I add to Access produces this oddity as I am struggling to find out exactly how best to write a query that also includes the start and end dates.

If it is just a matter of using the +1 option that is fine.

Hopefully I have clarified matters a bit better now and by the way I am a 'he!'

Regards. Pari.

 

[Brianwarnock]

Yes, I am very curious as to why I would actually have to use the +1 option when I just though I could have used some form of command instead. Also there is no time field in my data.

The Datediff is the function (command if you like) to calculate the difference between dates in various ways, but it uses simple arithmetic , so March to May is 5-3 = 2 but you want inclusive months so you need to add 1.

The Between .. AND..... is used in criteria to select records so that if you wanted to Select records that expire in the next 2 calendar Months then in the expiry date column criteria
Between Dateserial(Year(Date()),month(Date())+1,1) And Dateserial(Year(Date()),Month(Date())+3,0)
A day number 0 gives the last day of the previous Month.

Run today that would select for January and February 2010

Hope this helps.

Brian

 

[Pari]

Thanks for the detailed clarification Brian,

Things are a lot more clearer now. Also learnt a new command (dateserial)

Regards. Pari.