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 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
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.
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.
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.
"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
"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!)
"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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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 ...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |