Equal to or Greater than this date...

[tinyevil777]

Hey!

I've created a query, its basically querying a table containing sales records.

I have entered "Between [Start Date] and [End Date]" in the criteria for the Date field, as i want the user to specify the date range.

However, when i enter the Start Date as 28/09/10 and End Date as 29/09/10, it only displays records for 28/09/10, although there are records for 29/09/10.

I assume there's something wrong with my Between statement, but i can't work out what?

Could someone shed some light onto this for me please?

Thank you in advance!

Tom

 

[CBrighton]

Ah, the question of whether between is inclusive of the first & last dates or shows the data inbetween.

The amount of times this has caused me problems.

You could alwas change it to:

>[Start Date]-1 AND <[End Date]+1

This would mean later than the day before the date entered by the user and earlier than 1 day after the end date they enter. Therefore the results would be both dates input and any inbetween.

If the same date were entered for each it would display that date's data as only that date is after the day before and before the day after.

 

[tinyevil777]

Thanks for that reply! I just tried your suggestion, however i get the familiar "This expression is typed incorrectly" error message.

Would it be possible to use

 => [Start Date] AND <= [End Date]

?

I've tried this, but can't get it working.

 

[CBrighton]

Try #[Start Date]# instead of [Start Date], and the same for [End Date].

They are not needed with between but may be with > / <.

Trial & error, that's the way to learn!

 

[gemma-the-husky]

try

between [start date] and [end date]+1

the problem is that you have must have a time element in your "dates" (if you use now(), you get a time element - if you use date() you just get the date.

so say you have a startdate of 1/1/11 , and an end date of 31/1/11 - there is no problem with the start date, but the end date cuts off at midnight on 30/1/11. so you only catch dates on 31/1/11 with no time element.

 

[tinyevil777]

Thanks for both of your suggestions, unfortunately neither of them worked. "#[Start Date]#" returned an invalid expression, and "between [start date] and [end date]+1" returned a "This expression is typed incorrectly" error.


I've got absolutely no further ideas how to get this working. I'm sure we're missing something really simple that's escaping us...

 

[vbaInet]

between [start date] and DateAdd("d", 1, [end date])

 

[tinyevil777]

That's it! Thank you!

Could i be so cheeky as to ask you to explain your code?

 

[vbaInet]

There's not a lot to explain except the DateAdd() function which adds 1 to the day part of a date.

Perhaps the explanation on this link will provide more insight:

http://www.techonthenet.com/access/functions/date/dateadd.php

 

[Brianwarnock]

Between [start date] And [end date] + 1
works , as the system said you must have typed it incorrectly.

you should of course add #23:59:59# so that you don't catch the following day with 00:00:00 or even no time element which defaults to same.

Brian

 

[revsuperpowers]

How do you add the time to the expression?
Between [start date] And [end date] + 1

Thanks!

 

[Brianwarnock]

Between [start date] And [end date]+#23:59:59#

however you must ensure that the parameters are defined as type Date/Time
you can do this simply in SQL view by adding the first line

PARAMETERS [start date] DateTime, [end date] DateTime;

or in the design grid, select query from the menu bar,
then parameters and fill in the blanks

Brian

 

[Pat Hartman]

Whenever your stored date might contain a time because you used Now() as the date value rather than Date(), you can use the DateValue() function to extract only the date.

Where DateValue(mydate) Between [EnterFromDate] and [EnterThruDate];

Of course TimeValue() will extract only the time.

VBA provides a number of date functions. It would be in your best interest to review them so you know what is available.