473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

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
8 14175
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
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
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
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
"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
"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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Kevin | last post by:
When I run the query (please see below) between 08:30:00 and 17:29:59, I get the data from row 1; if I run the same query between 17:30:00 and 08:29:59, I get the "empty set." Can anyone explain...
3
by: karthik | last post by:
Does anybody know what is the limit for the number of values one can have in a list of expressions (to test for a match) for the IN clause? For example, SELECT au_lname, state FROM authors...
16
by: Bernd Hohmann | last post by:
Hi there, we're currently convert an old ISAM application to Java and DB2. Among the load of problems we found the reports which have user selections like "select all data between mm/yyyy and...
4
by: Greg Iocco | last post by:
Simple problem, but I've checked the newsgroups and couldn't find an answer. On the main swithboard is a command button to open a report. The report I built is based off a query, and the query...
1
by: Jaycee66 | last post by:
Access 2000 "Between" query question: I am relatively new to MS Access and everytime I think I have a basic concept of the program it pokes me in the eye with something I think would be simple to...
3
by: Jim | last post by:
I'm having a problem with a date query..im trying to pull customer data based on a date specified from a form to 3 months prior to the date specified. So lets say in the form I specified 1/2/2004....
11
by: MLH | last post by:
I have a query field... Description: & "2-tone" When I run it, the output in that field is empty. If I change the name to MyDescription - it works fine. I noticed the query field has a...
4
by: mukesh | last post by:
One another problem I have used a formula in the calculated field of a query as - IIf( <=, (IIf(<=, , ), (IIf (<=, , )) the above formula is working properly to give the lowest of , and ...
92
by: ureuffyrtu955 | last post by:
Python is a good programming language, but "Python" is not a good name. First, python also means snake, Monty Python. If we search "python" in google, emule, many results are not programming...
15
by: =?ISO-8859-15?Q?L=E9na=EFc?= Huard | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello all, For some reasons, somewhere in a program, I'd like, if possible, to quickly parse a whole file before rewinding it and letting the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.