QUERY Date Criteria

[Guest]

Good afternoon,

I am currently using "Between [Enter Start Date:] And [Enter End Date:]" as
criteria for a query to find orders between 2 dates, but I notice this
doesn't include the End date in the results unless I go one date past the end
date. For instance if I've got 2 orders entered on Feb 2, and I enter 2/2 for
my start date and 2/2 as my End date, no records apprear. But if I enter 2/2
for a start date and 2/3 as an End date, then these records appear. Is there
a way to include records for the start and End dates in the results, for ex.,
list if date > = to start date and <= End date? Thanks.

 

[Guest]

I'm not sure, and please if any one can correct me.
Mybe the field includes time, in that case try and use the format to remove
the time

Format([MyField],"dd/mm/yyyy") Between Format([Enter Start
Date:],"dd/mm/yyyy") And Format([Enter End Date:],"dd/mm/yyyy")

 

[Brendan Reynolds]

= Start date and < end date + 1

The issue is time. "Between 1 July 2005 AND 31 July 2005" is interpreted as
"between 1 July 2005 00:00:00 and 31 July 2005 00:00:00" and therefore will
not include any record where the date is, for example, 31 July 2005 00:00:01
or later.

 

[George Nicholson]

Does your OrderDate/OrderTime field consists of both Date and Time
components?

Dates, unless you specify otherwise, default to 12:00 am (midnight).

"Between 2/2 and 2/2" = Between #2/2 12:00 am# and #2/2 12:00 am#
(Inclusive) = probably not much of anything unless your stored dates don't
include any Time component (in which case you wouldn't have had a reason to
post).

EndDate needs to represent the beginning of the next day (2/3 not 2/2 if you
want to include the orders for 2/2)

HTH,

 

[Douglas J. Steele]

Using Format like that is not a good idea unless you use yyyymmdd as the
format.

That's because Format changes the date to text, and 02/12/2005 is less than
03/01/2005 when compare as text.

The other responders have the right idea.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm not sure, and please if any one can correct me.
Mybe the field includes time, in that case try and use the format to
remove
the time

Format([MyField],"dd/mm/yyyy") Between Format([Enter Start
Date:],"dd/mm/yyyy") And Format([Enter End Date:],"dd/mm/yyyy")

Good afternoon,

I am currently using "Between [Enter Start Date:] And [Enter End Date:]"
as
criteria for a query to find orders between 2 dates, but I notice this
doesn't include the End date in the results unless I go one date past the
end
date. For instance if I've got 2 orders entered on Feb 2, and I enter 2/2
for
my start date and 2/2 as my End date, no records apprear. But if I enter
2/2
for a start date and 2/3 as an End date, then these records appear. Is
there
a way to include records for the start and End dates in the results, for
ex.,
list if date > = to start date and <= End date? Thanks.

 

[Guest]

Thanks everybody, I'll give those a try. Also in the ">= Start date and < end
date + 1" statement, would I need to use a "Between" or will this work as ">=
[Enter Start Date:] and < [Enter End Date}+1"? Thanks.

= Start date and < end date + 1

The issue is time. "Between 1 July 2005 AND 31 July 2005" is interpreted as
"between 1 July 2005 00:00:00 and 31 July 2005 00:00:00" and therefore will
not include any record where the date is, for example, 31 July 2005 00:00:01
or later.

--
Brendan Reynolds (MVP)

Good afternoon,

I am currently using "Between [Enter Start Date:] And [Enter End Date:]"
as
criteria for a query to find orders between 2 dates, but I notice this
doesn't include the End date in the results unless I go one date past the
end
date. For instance if I've got 2 orders entered on Feb 2, and I enter 2/2
for
my start date and 2/2 as my End date, no records apprear. But if I enter
2/2
for a start date and 2/3 as an End date, then these records appear. Is
there
a way to include records for the start and End dates in the results, for
ex.,
list if date > = to start date and <= End date? Thanks.

 

[George Nicholson]

Between is inclusive.

Between [Enter Start Date:] and ([Enter End Date] + 0.99999)
(up to and including 11:59:59 pm of End Date)

Between [Enter Start Date:] and ([Enter End Date] + 1)
(up to and including midnight of what is technically the day following
EndDate.)

This would also include any "next day" records that have no time value.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Thanks everybody, I'll give those a try. Also in the ">= Start date and <
end
date + 1" statement, would I need to use a "Between" or will this work as
">=
[Enter Start Date:] and < [Enter End Date}+1"? Thanks.

= Start date and < end date + 1

The issue is time. "Between 1 July 2005 AND 31 July 2005" is interpreted
as
"between 1 July 2005 00:00:00 and 31 July 2005 00:00:00" and therefore
will
not include any record where the date is, for example, 31 July 2005
00:00:01
or later.

--
Brendan Reynolds (MVP)

Good afternoon,

I am currently using "Between [Enter Start Date:] And [Enter End
Date:]"
as
criteria for a query to find orders between 2 dates, but I notice this
doesn't include the End date in the results unless I go one date past
the
end
date. For instance if I've got 2 orders entered on Feb 2, and I enter
2/2
for
my start date and 2/2 as my End date, no records apprear. But if I
enter
2/2
for a start date and 2/3 as an End date, then these records appear. Is
there
a way to include records for the start and End dates in the results,
for
ex.,
list if date > = to start date and <= End date? Thanks.

 

[Van T. Dinh]

No, don't use BETWEEN ... AND ... with >= and <.

HTH
Van T. Dinh
MVP (Access)

 

[Guest]

You right, I forgot the cvdate before it, buy if I'm not wrong it will still
work the way it is, because it a variant type, I've been using it in few
occasions and it works perfertly

cvdate(Format([MyField],"dd/mm/yyyy")) Between cvdate(Format([Enter Start
Date:],"dd/mm/yyyy") ) And cvdate(Format([Enter End Date:],"dd/mm/yyyy"))

Using Format like that is not a good idea unless you use yyyymmdd as the
format.

That's because Format changes the date to text, and 02/12/2005 is less than
03/01/2005 when compare as text.

The other responders have the right idea.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm not sure, and please if any one can correct me.
Mybe the field includes time, in that case try and use the format to
remove
the time

Format([MyField],"dd/mm/yyyy") Between Format([Enter Start
Date:],"dd/mm/yyyy") And Format([Enter End Date:],"dd/mm/yyyy")

Good afternoon,

I am currently using "Between [Enter Start Date:] And [Enter End Date:]"
as
criteria for a query to find orders between 2 dates, but I notice this
doesn't include the End date in the results unless I go one date past the
end
date. For instance if I've got 2 orders entered on Feb 2, and I enter 2/2
for
my start date and 2/2 as my End date, no records apprear. But if I enter
2/2
for a start date and 2/3 as an End date, then these records appear. Is
there
a way to include records for the start and End dates in the results, for
ex.,
list if date > = to start date and <= End date? Thanks.

 

[Douglas J Steele]

Again, a warning.

It works perfectly for you because you've apparently set your Short Date
format to dd/mm/yyyy in Regional Settings. It may not work for someone whose
Short Date format is set to something else. Since you can't guarantee the
settings chosen by all of your users, a more generic approach is preferable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You right, I forgot the cvdate before it, buy if I'm not wrong it will still
work the way it is, because it a variant type, I've been using it in few
occasions and it works perfertly

cvdate(Format([MyField],"dd/mm/yyyy")) Between cvdate(Format([Enter Start
Date:],"dd/mm/yyyy") ) And cvdate(Format([Enter End Date:],"dd/mm/yyyy"))

Using Format like that is not a good idea unless you use yyyymmdd as the
format.

That's because Format changes the date to text, and 02/12/2005 is less than
03/01/2005 when compare as text.

The other responders have the right idea.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm not sure, and please if any one can correct me.
Mybe the field includes time, in that case try and use the format to
remove
the time

Format([MyField],"dd/mm/yyyy") Between Format([Enter Start
Date:],"dd/mm/yyyy") And Format([Enter End Date:],"dd/mm/yyyy")

:

Good afternoon,

I am currently using "Between [Enter Start Date:] And [Enter End Date:]"
as
criteria for a query to find orders between 2 dates, but I notice this
doesn't include the End date in the results unless I go one date past the
end
date. For instance if I've got 2 orders entered on Feb 2, and I enter 2/2
for
my start date and 2/2 as my End date, no records apprear. But if I enter
2/2
for a start date and 2/3 as an End date, then these records appear. Is
there
a way to include records for the start and End dates in the results, for
ex.,
list if date > = to start date and <= End date? Thanks.

 

[\Chamud a via AccessMonster.com\]

You are so right, I'm sorry for the prev post.
My mistake is that I never use parameters in a query, I always use dynamic
SQL, so when I'm entering the criteria I write

Where MyDate Between #" & Format(Param1,"dd/mm/yyyy") & "# And #" & Format
(param2,"dd/mm/yyyy") & "#"

So I got wrealy confused, sorry about that, and thank you for your time and
help.