Equal to or Greater than this date... (1 Viewer)

tinyevil777

Registered User.
Local time
Today, 21:13
Joined
Dec 10, 2010
Messages
137
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

Surfing while working...
Local time
Today, 21:13
Joined
Nov 9, 2010
Messages
1,012
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:
Code:
>[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

Registered User.
Local time
Today, 21:13
Joined
Dec 10, 2010
Messages
137
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
Code:
 => [Start Date] AND <= [End Date]
?

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

CBrighton

Surfing while working...
Local time
Today, 21:13
Joined
Nov 9, 2010
Messages
1,012
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

Super Moderator
Staff member
Local time
Today, 21:13
Joined
Sep 12, 2006
Messages
15,614
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

Registered User.
Local time
Today, 21:13
Joined
Dec 10, 2010
Messages
137
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

AWF VIP
Local time
Today, 21:13
Joined
Jan 22, 2010
Messages
26,374
Code:
between [start date] and DateAdd("d", 1, [end date])
 

tinyevil777

Registered User.
Local time
Today, 21:13
Joined
Dec 10, 2010
Messages
137
That's it! Thank you!

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

Brianwarnock

Retired
Local time
Today, 21:13
Joined
Jun 2, 2003
Messages
12,701
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
 
Last edited:

revsuperpowers

New member
Local time
Today, 14:13
Joined
Jan 18, 2013
Messages
1
How do you add the time to the expression?
Between [start date] And [end date] + 1

Thanks!
 

Brianwarnock

Retired
Local time
Today, 21:13
Joined
Jun 2, 2003
Messages
12,701
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

Super Moderator
Staff member
Local time
Today, 17:13
Joined
Feb 19, 2002
Messages
42,976
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.
 

Users who are viewing this thread

Top Bottom