By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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


Nov 13 '05 #2

P: n/a
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.
Nov 13 '05 #3

P: n/a
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.
Nov 13 '05 #4

P: n/a
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******@example.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.

Nov 13 '05 #5

P: n/a
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:PR*******************@news.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

Nov 13 '05 #6

P: n/a
"Bruno Campanini" <br*************@tin.it> wrote in message
news:Lf********************@news3.tin.it...
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:PR*******************@news.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!)
Nov 13 '05 #7

P: n/a
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:gO********************@rogers.com...
"Bruno Campanini" <br*************@tin.it> wrote in message
news:Lf********************@news3.tin.it...
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:PR*******************@news.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
Nov 13 '05 #8

P: n/a
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_djsteele@NOSPAM_canada.com> wrote in message
news:gO********************@rogers.com...
"Bruno Campanini" <br*************@tin.it> wrote in message
news:Lf********************@news3.tin.it...
"Paul T. Rong" <et***@hotmail.com> wrote in message
news:PR*******************@news.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


Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.