problem to use "between ... and..." in a query

[Paul T. Rong]

I used "between [StartingDate] and [EndingDate]" in a query as a criteria to
a field called ShipDate.
Very strange, in the case between 1.10.2003 and 31.10.2003, then the records
from 31.10.2003 don't show up. I have to modify it as between 1.10.2003 and
1.11.2003 for the records from 31.10.2003 show up.

anybody had same problem? I check the Access Help but didn't get any clue.

Thanks in advance

Paul

[Patrick Fisher]

Have you tried

=DateFrom and <=DateTwo instead of between? Patrick

On Fri, 15 Oct 2004 17:33:37 GMT, "Paul T. Rong" <et***@hotmail. com>
wrote:
I used "between [StartingDate] and [EndingDate]" in a query as a criteria to
a field called ShipDate.
Very strange, in the case between 1.10.2003 and 31.10.2003, then the records
from 31.10.2003 don't show up. I have to modify it as between 1.10.2003 and
1.11.2003 for the records from 31.10.2003 show up.

anybody had same problem? I check the Access Help but didn't get any clue.

Thanks in advance

Paul

[Salad]

Paul T. Rong wrote:

I used "between [StartingDate] and [EndingDate]" in a query as a criteria to
a field called ShipDate.
Very strange, in the case between 1.10.2003 and 31.10.2003, then the records
from 31.10.2003 don't show up. I have to modify it as between 1.10.2003 and
1.11.2003 for the records from 31.10.2003 show up.

anybody had same problem? I check the Access Help but didn't get any clue.

Thanks in advance

Paul

If you assign a value of Date() to a field, the time is 00:00:00

If you assign a value of Now() to a field, the time is whatever time it
was when saved.

So...if you attempt to select records in 10/31/2004, and you are asking
for 00:00:00 then a record of 10/31/2004 00:00:01 will not be selected.

That is why you need to add another day...because you are then selecting
records less than 11/01/2004 00:00:00.

[fredg]

On Fri, 15 Oct 2004 17:33:37 GMT, Paul T. Rong wrote:

I used "between [StartingDate] and [EndingDate]" in a query as a criteria to
a field called ShipDate.
Very strange, in the case between 1.10.2003 and 31.10.2003, then the records
from 31.10.2003 don't show up. I have to modify it as between 1.10.2003 and
1.11.2003 for the records from 31.10.2003 show up.

anybody had same problem? I check the Access Help but didn't get any clue.

Thanks in advance

Paul

If, in using Between [Start Date] and [End Date] , you don't get the
final day, it is probably because your date field is storing a time
value as well as a date value.

Entering #07/08/2004# as an [End Date] will return records up to
Midnight of that date, and so no records appear to be returned (unless
you do actually have a date and time value of exactly midnight.

Either update your table to remove the time value,
or manually enter one day later to the wanted period,
or use:

Between [Start Date] and ([End Date]+1)

In which case you must enter the [Start Date] and [End Date] as
Date/Time parameters in the Query Parameter dialog box:
In Query Design View, click Query + Parameter

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Paul T. Rong]

Thanks to Patrick, Salad, and fredg. I understand the problem now and think
what fredg said --- to use [End Date]+1 --- should be a very good solution.

Paul

"fredg" <fg******@examp le.invalid>
??????:os****** *************** *******@40tude. net...

On Fri, 15 Oct 2004 17:33:37 GMT, Paul T. Rong wrote:

I used "between [StartingDate] and [EndingDate]" in a query as a criteria to a field called ShipDate.
Very strange, in the case between 1.10.2003 and 31.10.2003, then the records from 31.10.2003 don't show up. I have to modify it as between 1.10.2003 and 1.11.2003 for the records from 31.10.2003 show up.

anybody had same problem? I check the Access Help but didn't get any clue.
Thanks in advance

Paul

If, in using Between [Start Date] and [End Date] , you don't get the
final day, it is probably because your date field is storing a time
value as well as a date value.

Entering #07/08/2004# as an [End Date] will return records up to
Midnight of that date, and so no records appear to be returned (unless
you do actually have a date and time value of exactly midnight.

Either update your table to remove the time value,
or manually enter one day later to the wanted period,
or use:

Between [Start Date] and ([End Date]+1)

In which case you must enter the [Start Date] and [End Date] as
Date/Time parameters in the Query Parameter dialog box:
In Query Design View, click Query + Parameter

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Bruno Campanini]

"Paul T. Rong" <et***@hotmail. com> wrote in message
news:PR******** ***********@new s.chello.at...

Thanks to Patrick, Salad, and fredg. I understand the problem now and
think
what fredg said --- to use [End Date]+1 --- should be a very good
solution.

Paul

But it's not the correct one.
If you have [End Date] set in your table as Date/Time
" ... WHERE (((Table1.[End Date]) Between #10/1/2003# And #10/31/2003#));"
must work.

Bruno

[Douglas J. Steele]

"Bruno Campanini" <br************ *@tin.it> wrote in message
news:Lf******** ************@ne ws3.tin.it...

"Paul T. Rong" <et***@hotmail. com> wrote in message
news:PR******** ***********@new s.chello.at...

Thanks to Patrick, Salad, and fredg. I understand the problem now and
think
what fredg said --- to use [End Date]+1 --- should be a very good
solution.

Paul

But it's not the correct one.
If you have [End Date] set in your table as Date/Time
" ... WHERE (((Table1.[End Date]) Between #10/1/2003# And #10/31/2003#));"
must work.

Reread what Salad posted, Bruno. If End Date includes a time, using an end
date of 10/31/2003 will not retrieve any records for the 31st unless they
happened to be exactly at midnight.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

[Bruno Campanini]

"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:gO******** ************@ro gers.com...

"Bruno Campanini" <br************ *@tin.it> wrote in message
news:Lf******** ************@ne ws3.tin.it...

"Paul T. Rong" <et***@hotmail. com> wrote in message
news:PR******** ***********@new s.chello.at...

> Thanks to Patrick, Salad, and fredg. I understand the problem now and
> think
> what fredg said --- to use [End Date]+1 --- should be a very good
> solution.
>
> Paul

But it's not the correct one.
If you have [End Date] set in your table as Date/Time
" ... WHERE (((Table1.[End Date]) Between #10/1/2003# And
#10/31/2003#));"
must work.

Reread what Salad posted, Bruno. If End Date includes a time, using an end
date of 10/31/2003 will not retrieve any records for the 31st unless they
happened to be exactly at midnight.

Ok, well understood now.
Thanks
Bruno

[Patrick Fisher]

That was very useful to me too
Patrick

On Sat, 16 Oct 2004 14:53:22 GMT, "Bruno Campanini"
<br************ *@tin.it> wrote:

"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:gO******* *************@r ogers.com...

"Bruno Campanini" <br************ *@tin.it> wrote in message
news:Lf******** ************@ne ws3.tin.it...

"Paul T. Rong" <et***@hotmail. com> wrote in message
news:PR******** ***********@new s.chello.at...
> Thanks to Patrick, Salad, and fredg. I understand the problem now and
> think
> what fredg said --- to use [End Date]+1 --- should be a very good
> solution.
>
> Paul

But it's not the correct one.
If you have [End Date] set in your table as Date/Time
" ... WHERE (((Table1.[End Date]) Between #10/1/2003# And
#10/31/2003#));"
must work.

Reread what Salad posted, Bruno. If End Date includes a time, using an end
date of 10/31/2003 will not retrieve any records for the 31st unless they
happened to be exactly at midnight.

Ok, well understood now.
Thanks
Bruno