How to report on inclusive dates

[Pari]

I am aware you can use the BETWEEN command to report on a date range however I am unsure how you would actually add this to the query that I have below:

Expr1: Int((DateDiff('d',[issue_date],[expiry_date]))

As the problem with the above equation is that it does not on some occassions inlcude the start and end date. I know that you can add +2 to the end but I was really hoping to made use of the BETWEEN command if possible.

Can this be done?

Regards. Pari.

 

[DCrake]

Take a look at this Link it may answer your question.

David

 

[wilpeter]

David: Thanks for your intervention on NumLock's thread last evening. I didn't notice immediately that it was yours (thinking it was the poster). In the above example for this thread, I'm unable to open the Public Variables database (due to protection issues in my system) but I understood the Word document explanation. Excellent idea. I'm using Access 2002 SR3 with XP and don't know how to bypass the limitation "This file is located outside your intranet or on an untrusted site. etc."

 

[Pari]

Thank you David,

I have taken a look at both the sample database and also the word document but I am still strugling with this issue I am afriad.

Maybe I have not been that clear in what I am actually after as I am trying to make sure the above query pulls in the start and end date as well.

This would then eliminate the need to add +2 at the end of the query.

Would you have any ideas how I would do this please?

 

[JohnLee]

Hi,

In The criteria row directly beneath your date field enter something like this:

Between [Enter Start Date] And [Enter End Date]

The above will always include the chosen start date and chosen end date.

If you want to be specific about the date range, then something like this:

Between #11/05/2005# And #15/10/2008#

The first date and last date will always be included in the query result as per the first example.

Hope this is of assistance

John

 

[Brianwarnock]

I don't understand the +2, well actually I didn't understand the 1st post.
However note that if fields contain times but you only quote a date in the criteria then the time is defaulted to 00:00:00 and thus the last day is not included, hence people add 1 althougth to be strictly correct they should add 23:59:59, but if nothing happens at mid night adding 1 day is fine.

Brian

 

[Pari]

Thanks John,

While this does work for some occassion I am really trying to resolve the issue with using the datediff command as it for some reason does not count the start and end dates of the query.

 

[Brianwarnock]

I think that you need to explain exactly what you are trying to do.

What has Datediff got to do with Between ... And...
Why have you got INT in this expression
Expr1: Int((DateDiff('d',[issue_date],[expiry_date]))
You mention Start and End dates but they are , well where.?

Give the full SQL of your query as well as an explanation of your wishes.

Brian

 

[wilpeter]

Brian: Based on what Pari has said so far, he appears to want the whole number of days lapsed from Issue Date to Expiry Date. If this is insurance, however, our policies in Canada expire at 12:01 midnight, so don't include the last date. Pari should likely confirm, however.

 

[Pari]

Dear all,

Apoligies for the lack of clarification.

Yes I would like my report to also include the inclusive dates as at the moment I have to add 2 to the above expression to be able to identify how many months have elapsed.

That is the reason I used the BETWEEN function, which I now know was done in error.

Sorry but I am sure you have guessed by now that I am no Access expert!

Thanks and kind regards. Pari.

 

[wilpeter]

Just when I thought we had it, you've muddied the waters again: I posited that you wanted the total days between A and B dates. Now it sounds like you want the total months (inclusive) as a whole number. Thus from 29 Nov to 3 Dec would be 2 ?

 

[Pari]

Wilpeter,

You are currect in your assumption. Is there a query that help with this?

Regards. Pari.

 

[Brianwarnock]

I think datediff("m",issuedate,expirydate) +1 should do the trick

Brian

 

[wilpeter]

And, in case you hadn't guessed, that makes it a calculated field instead of criteria.

 

[Brianwarnock]

It might help if Pari hung around for a bit, but he or she pops in and then disappears for a few days.

brian

 

[Pari]

Thanks for that Brian,

That does work but do you know of any command that can also be used, like BETWEEN?

Regards. Pari.

 

[wilpeter]

Pari: I'm curious why you would want to avoid this particular solution. You could create a new expression through code than combines the functions that exist in Excel for example, like RoundUp; but why?

 

[boblarson]

Thanks for that Brian,

That does work but do you know of any command that can also be used, like BETWEEN?

Regards. Pari.

BETWEEN IS inclusive but you have to realize what you're doing when you do it. If your date field includes time then the start date when used with BETWEEN will be midnight on the date selected (unless otherwise specified):

BETWEEN #12/23/2009# (will be interpreted as BETWEEN #12/23/2009 12:00:00 AM#)

And then Ending date is thus (if with time and no time specified: #12/31/2009# will be interpreted as #12/31/2009 12:00:00 AM# which obviously leaves out the entire day of 12/31/2009).

So, if you have a time element to your date field, you should use the time component as well OR know you have to add one to get what you want:

BETWEEN #12/23/2009 12:00:00 AM# AND #12/31/2009 11:59:59#

 

[raskew]

Hi again -

You might want to play around with this as an alternative ending date/time:

? dateadd("s", -1, dateserial(year(date()), month(date())+1, 1))
12/31/2009 11:59:59 PM

Bob

 

[boblarson]

Hi again -

You might want to play around with this as an alternative ending date/time:

? dateadd("s", -1, dateserial(year(date()), month(date())+1, 1))
12/31/2009 11:59:59 PM

Bob

And that's good stuff as usual. The point I was trying to make, however, is that BETWEEN works just as it should. The problem comes when people don't realize what is happening when they have a time component besides date. When used with dates that have no time component, it works just fine. But when used with a time component, it throws people off because they aren't expecting it to use the time component but it does and defaults to one if one isn't supplied.