date/time dilemma

  • Thread starter Thread starter RedFred
  • Start date Start date
R

RedFred

Table has fields [EnterDate] and [StartDate]. Each is
date/time, m/d/yy, indexed dupes ok.

Query 1 retrieves [EnterDate]; query 2 [StartDate].

I limit return by using "BETWEEN #9/1/04# AND #9/13/04#.

Problem:
Running query 2 returns all records, INcluding 9/13.
Running query 1 returns all records, EXcluding 9/13.

Everything appears the same except for the lack of 9/13
records for query 1. I don't get it.

In hopes that someone can see a flaw...

I thank you for your assitance.
RedFred
 
Table has fields [EnterDate] and [StartDate]. Each is
date/time, m/d/yy, indexed dupes ok.

Query 1 retrieves [EnterDate]; query 2 [StartDate].

I limit return by using "BETWEEN #9/1/04# AND #9/13/04#.

Problem:
Running query 2 returns all records, INcluding 9/13.
Running query 1 returns all records, EXcluding 9/13.

Everything appears the same except for the lack of 9/13
records for query 1. I don't get it.

In hopes that someone can see a flaw...

I thank you for your assitance.
RedFred
Click to expand...

If the date field in Query 1 has a time value included in the field,
then searching for records ending on 9/13/2004 will not find any past
9/13/2004 00:00:00 AM.

Among your options are to change the search value to 1 day later
(9/14/2004), or include a time value #9/13/2004 23:59:59 PM#, or
change the field data to remove the time value from the data if it's
not otherwise needed. An Update query will do this painlessly.
 
Fred, the RedFred thanks you. I'll give it a shot -- it
makes sense.

-----Original Message-----
Table has fields [EnterDate] and [StartDate]. Each is
date/time, m/d/yy, indexed dupes ok.

Query 1 retrieves [EnterDate]; query 2 [StartDate].

I limit return by using "BETWEEN #9/1/04# AND #9/13/04#.

Problem:
Running query 2 returns all records, INcluding 9/13.
Running query 1 returns all records, EXcluding 9/13.

Everything appears the same except for the lack of 9/13
records for query 1. I don't get it.

In hopes that someone can see a flaw...

I thank you for your assitance.
RedFred
Click to expand...

If the date field in Query 1 has a time value included in the field,
then searching for records ending on 9/13/2004 will not find any past
9/13/2004 00:00:00 AM.

Among your options are to change the search value to 1 day later
(9/14/2004), or include a time value #9/13/2004 23:59:59 PM#, or
change the field data to remove the time value from the data if it's
not otherwise needed. An Update query will do this painlessly.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
I see how that works, Fred, but how about when I use it
this way? How do I limit as I do with the BETWEEN ". . .
23:59:59 PM#"

strSQL = "[EnterDate] >= #" & CDate(Me!txtStartDate) & "#
AND [EnterDate] <= #" & CDate(Me!txtEndDate) & "#"

I mean, how do I qualify this statement like in the query?

Thanks.
-----Original Message-----
Table has fields [EnterDate] and [StartDate]. Each is
date/time, m/d/yy, indexed dupes ok.

Query 1 retrieves [EnterDate]; query 2 [StartDate].

I limit return by using "BETWEEN #9/1/04# AND #9/13/04#.

Problem:
Running query 2 returns all records, INcluding 9/13.
Running query 1 returns all records, EXcluding 9/13.

Everything appears the same except for the lack of 9/13
records for query 1. I don't get it.

In hopes that someone can see a flaw...

I thank you for your assitance.
RedFred
Click to expand...

If the date field in Query 1 has a time value included in the field,
then searching for records ending on 9/13/2004 will not find any past
9/13/2004 00:00:00 AM.

Among your options are to change the search value to 1 day later
(9/14/2004), or include a time value #9/13/2004 23:59:59 PM#, or
change the field data to remove the time value from the data if it's
not otherwise needed. An Update query will do this painlessly.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Hi,



strSQL= " ... AND [EnterDate] < 1+ " & Format( ..., "\#mm-dd-yyyy\#" )


If you don't force the American format, you may be due for hard to debug
error if you application is used else where in the world (including Canada,
UK, Australia, etc).


Hoping it may help,
Vanderghast, Access MVP


RedFred said:
I see how that works, Fred, but how about when I use it
this way? How do I limit as I do with the BETWEEN ". . .
23:59:59 PM#"

strSQL = "[EnterDate] >= #" & CDate(Me!txtStartDate) & "#
AND [EnterDate] <= #" & CDate(Me!txtEndDate) & "#"

I mean, how do I qualify this statement like in the query?

Thanks.
-----Original Message-----
Table has fields [EnterDate] and [StartDate]. Each is
date/time, m/d/yy, indexed dupes ok.

Query 1 retrieves [EnterDate]; query 2 [StartDate].

I limit return by using "BETWEEN #9/1/04# AND #9/13/04#.

Problem:
Running query 2 returns all records, INcluding 9/13.
Running query 1 returns all records, EXcluding 9/13.

Everything appears the same except for the lack of 9/13
records for query 1. I don't get it.

In hopes that someone can see a flaw...

I thank you for your assitance.
RedFred
Click to expand...

If the date field in Query 1 has a time value included in the field,
then searching for records ending on 9/13/2004 will not find any past
9/13/2004 00:00:00 AM.

Among your options are to change the search value to 1 day later
(9/14/2004), or include a time value #9/13/2004 23:59:59 PM#, or
change the field data to remove the time value from the data if it's
not otherwise needed. An Update query will do this painlessly.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...
 
Thanks, but I am still not getting it.

How does it work without the format force? I'm just an
amateur and there ceratainly won't be any user but my
employees. US, they are.

I thought I understood when the change was "<=" to "<1+";
makes some sense. Don't know coding nearly well enough
though. No combination I tried worked, with or without
the format change.

Have you got more help in ya? Hope!

Thanks.


-----Original Message-----
Hi,



strSQL= " ... AND [EnterDate] < 1+ " & Format ( ..., "\#mm-dd-yyyy\#" )


If you don't force the American format, you may be due for hard to debug
error if you application is used else where in the world (including Canada,
UK, Australia, etc).


Hoping it may help,
Vanderghast, Access MVP


I see how that works, Fred, but how about when I use it
this way? How do I limit as I do with the BETWEEN ". . .
23:59:59 PM#"

strSQL = "[EnterDate] >= #" & CDate(Me!txtStartDate) & "#
AND [EnterDate] <= #" & CDate(Me!txtEndDate) & "#"

I mean, how do I qualify this statement like in the query?

Thanks.
-----Original Message-----
On Mon, 13 Sep 2004 09:59:43 -0700, RedFred wrote:

Table has fields [EnterDate] and [StartDate]. Each is
date/time, m/d/yy, indexed dupes ok.

Query 1 retrieves [EnterDate]; query 2 [StartDate].

I limit return by using "BETWEEN #9/1/04# AND #9/13/04#.

Problem:
Running query 2 returns all records, INcluding 9/13.
Running query 1 returns all records, EXcluding 9/13.

Everything appears the same except for the lack of 9/13
records for query 1. I don't get it.

In hopes that someone can see a flaw...

I thank you for your assitance.
RedFred

If the date field in Query 1 has a time value included in the field,
then searching for records ending on 9/13/2004 will not find any past
9/13/2004 00:00:00 AM.

Among your options are to change the search value to 1 day later
(9/14/2004), or include a time value #9/13/2004
Click to expand...
23:59:59
PM#, or
change the field data to remove the time value from the data if it's
not otherwise needed. An Update query will do this painlessly.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi,


The end result should be:

BETWEEN #9/1/04# AND 1+ #9/13/04#



or (in one line):


strSQL = "[EnterDate] >= #" & CDate(Me!txtStartDate) & "#
AND [EnterDate] < 1+ #" & CDate(Me!txtEndDate) & "#"




Hoping it may help,
Vanderghast, Access MVP

RedFred said:
Thanks, but I am still not getting it.

How does it work without the format force? I'm just an
amateur and there ceratainly won't be any user but my
employees. US, they are.

I thought I understood when the change was "<=" to "<1+";
makes some sense. Don't know coding nearly well enough
though. No combination I tried worked, with or without
the format change.

Have you got more help in ya? Hope!

Thanks.


-----Original Message-----
Hi,



strSQL= " ... AND [EnterDate] < 1+ " & Format ( ..., "\#mm-dd-yyyy\#" )


If you don't force the American format, you may be due for hard to debug
error if you application is used else where in the world (including Canada,
UK, Australia, etc).


Hoping it may help,
Vanderghast, Access MVP


I see how that works, Fred, but how about when I use it
this way? How do I limit as I do with the BETWEEN ". . .
23:59:59 PM#"

strSQL = "[EnterDate] >= #" & CDate(Me!txtStartDate) & "#
AND [EnterDate] <= #" & CDate(Me!txtEndDate) & "#"

I mean, how do I qualify this statement like in the query?

Thanks.

-----Original Message-----
On Mon, 13 Sep 2004 09:59:43 -0700, RedFred wrote:

Table has fields [EnterDate] and [StartDate]. Each is
date/time, m/d/yy, indexed dupes ok.

Query 1 retrieves [EnterDate]; query 2 [StartDate].

I limit return by using "BETWEEN #9/1/04# AND
#9/13/04#.

Problem:
Running query 2 returns all records, INcluding 9/13.
Running query 1 returns all records, EXcluding 9/13.

Everything appears the same except for the lack of
9/13
records for query 1. I don't get it.

In hopes that someone can see a flaw...

I thank you for your assitance.
RedFred

If the date field in Query 1 has a time value included
in the field,
then searching for records ending on 9/13/2004 will not
find any past
9/13/2004 00:00:00 AM.

Among your options are to change the search value to 1
day later
(9/14/2004), or include a time value #9/13/2004 23:59:59
PM#, or
change the field data to remove the time value from the
data if it's
not otherwise needed. An Update query will do this
painlessly.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...


.
Click to expand...
Click to expand...
 
I really do appreciate the help, but I'm not getting
anything still.

Here is the code that does not return 9/13/04:
strSQL = "[EnterDate] >= #" & CDate(Me!
txtStartDate) & "# AND [EnterDate] < 1+ #" & CDate(Me!
txtEndDate) & "#"

I have other code that does fine (StartDate is 9/13/04):
strSQL = "[StartDate] >= #" & CDate(Me!
txtStartDate) & "# AND [StartDate] <= #" & CDate(Me!
txtEndDate) & "#"

EnterDate and StartDate are the set up the same in the
table.

Can you see anything here? Somewhere else maybe?

Thanks,
Fred

-----Original Message-----
Hi,


The end result should be:

BETWEEN #9/1/04# AND 1+ #9/13/04#



or (in one line):


strSQL = "[EnterDate] >= #" & CDate(Me!txtStartDate) & "#
AND [EnterDate] < 1+ #" & CDate(Me!txtEndDate) & "#"




Hoping it may help,
Vanderghast, Access MVP

Thanks, but I am still not getting it.

How does it work without the format force? I'm just an
amateur and there ceratainly won't be any user but my
employees. US, they are.

I thought I understood when the change was "<=" to "<1+";
makes some sense. Don't know coding nearly well enough
though. No combination I tried worked, with or without
the format change.

Have you got more help in ya? Hope!

Thanks.


-----Original Message-----
Hi,



strSQL= " ... AND [EnterDate] < 1+ " & Format ( ..., "\#mm-dd-yyyy\#" )


If you don't force the American format, you may be due for hard to debug
error if you application is used else where in the
Click to expand...
world
(including Canada,
UK, Australia, etc).


Hoping it may help,
Vanderghast, Access MVP


"RedFred" <[email protected]> wrote
Click to expand...
in
message
I see how that works, Fred, but how about when I use it
this way? How do I limit as I do with the BETWEEN ". . .
23:59:59 PM#"

strSQL = "[EnterDate] >= #" & CDate(Me!txtStartDate) & "#
AND [EnterDate] <= #" & CDate(Me!txtEndDate) & "#"

I mean, how do I qualify this statement like in the query?

Thanks.

-----Original Message-----
On Mon, 13 Sep 2004 09:59:43 -0700, RedFred wrote:

Table has fields [EnterDate] and [StartDate]. Each is
date/time, m/d/yy, indexed dupes ok.

Query 1 retrieves [EnterDate]; query 2 [StartDate].

I limit return by using "BETWEEN #9/1/04# AND
#9/13/04#.

Problem:
Running query 2 returns all records, INcluding 9/13.
Running query 1 returns all records, EXcluding 9/13.

Everything appears the same except for the lack of
9/13
records for query 1. I don't get it.

In hopes that someone can see a flaw...

I thank you for your assitance.
RedFred

If the date field in Query 1 has a time value included
in the field,
then searching for records ending on 9/13/2004 will not
find any past
9/13/2004 00:00:00 AM.

Among your options are to change the search value to 1
day later
(9/14/2004), or include a time value #9/13/2004 23:59:59
PM#, or
change the field data to remove the time value from the
data if it's
not otherwise needed. An Update query will do this
painlessly.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.



.
Click to expand...
Click to expand...


.
Click to expand...
 
HI,


What is [EnterDate] ? I assume it was a field in the table, and its
data type is date_time. In your second example, you use [StartDate], which
is not [EnterDate], but another field.

A date_time value is stored in number of days (and portion of day) since
30th December 1899. The fractional portion, such as 0.25, means a quarter of
a day, or 6 hour, as example. Adding 1 is, effectively, adding 24 hours.


Hoping it may help,
Vanderghast, Access MVP

RedFred said:
I really do appreciate the help, but I'm not getting
anything still.

Here is the code that does not return 9/13/04:
strSQL = "[EnterDate] >= #" & CDate(Me!
txtStartDate) & "# AND [EnterDate] < 1+ #" & CDate(Me!
txtEndDate) & "#"

I have other code that does fine (StartDate is 9/13/04):
strSQL = "[StartDate] >= #" & CDate(Me!
txtStartDate) & "# AND [StartDate] <= #" & CDate(Me!
txtEndDate) & "#"

EnterDate and StartDate are the set up the same in the
table.

Can you see anything here? Somewhere else maybe?

Thanks,
Fred

-----Original Message-----
Hi,


The end result should be:

BETWEEN #9/1/04# AND 1+ #9/13/04#



or (in one line):


strSQL = "[EnterDate] >= #" & CDate(Me!txtStartDate) & "#
AND [EnterDate] < 1+ #" & CDate(Me!txtEndDate) & "#"




Hoping it may help,
Vanderghast, Access MVP

Thanks, but I am still not getting it.

How does it work without the format force? I'm just an
amateur and there ceratainly won't be any user but my
employees. US, they are.

I thought I understood when the change was "<=" to "<1+";
makes some sense. Don't know coding nearly well enough
though. No combination I tried worked, with or without
the format change.

Have you got more help in ya? Hope!

Thanks.



-----Original Message-----
Hi,



strSQL= " ... AND [EnterDate] < 1+ " & Format
( ..., "\#mm-dd-yyyy\#" )


If you don't force the American format, you may be due
for hard to debug
error if you application is used else where in the world
(including Canada,
UK, Australia, etc).


Hoping it may help,
Vanderghast, Access MVP


message
I see how that works, Fred, but how about when I use it
this way? How do I limit as I do with the
BETWEEN ". . .
23:59:59 PM#"

strSQL = "[EnterDate] >= #" & CDate(Me!txtStartDate)
& "#
AND [EnterDate] <= #" & CDate(Me!txtEndDate) & "#"

I mean, how do I qualify this statement like in the
query?

Thanks.

-----Original Message-----
On Mon, 13 Sep 2004 09:59:43 -0700, RedFred wrote:

Table has fields [EnterDate] and [StartDate]. Each
is
date/time, m/d/yy, indexed dupes ok.

Query 1 retrieves [EnterDate]; query 2 [StartDate].

I limit return by using "BETWEEN #9/1/04# AND
#9/13/04#.

Problem:
Running query 2 returns all records, INcluding
9/13.
Running query 1 returns all records, EXcluding
9/13.

Everything appears the same except for the lack of
9/13
records for query 1. I don't get it.

In hopes that someone can see a flaw...

I thank you for your assitance.
RedFred

If the date field in Query 1 has a time value included
in the field,
then searching for records ending on 9/13/2004 will not
find any past
9/13/2004 00:00:00 AM.

Among your options are to change the search value to 1
day later
(9/14/2004), or include a time value #9/13/2004
23:59:59
PM#, or
change the field data to remove the time value from the
data if it's
not otherwise needed. An Update query will do this
painlessly.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.



.
Click to expand...


.
Click to expand...
Click to expand...
 
EnterDate and StartDate are fields in a table that have
identical setup. See first message. StartDate code was
provided to illustrate that the same code & the same setup
do not produce the same results. I have explained that in
earlier messages.

I understand the 24 hour thing, but cannot determine why,
when setup identically with the same exact code (or even
with your suggested +1 coding) these two behave so
differently, either including or excluding the last
selected day.

What kinds of things cause this?

Thanks,
RedFred
-----Original Message-----
HI,


What is [EnterDate] ? I assume it was a field in the table, and its
data type is date_time. In your second example, you use [StartDate], which
is not [EnterDate], but another field.

A date_time value is stored in number of days (and portion of day) since
30th December 1899. The fractional portion, such as 0.25, means a quarter of
a day, or 6 hour, as example. Adding 1 is, effectively, adding 24 hours.


Hoping it may help,
Vanderghast, Access MVP

I really do appreciate the help, but I'm not getting
anything still.

Here is the code that does not return 9/13/04:
strSQL = "[EnterDate] >= #" & CDate(Me!
txtStartDate) & "# AND [EnterDate] < 1+ #" & CDate(Me!
txtEndDate) & "#"

I have other code that does fine (StartDate is 9/13/04):
strSQL = "[StartDate] >= #" & CDate(Me!
txtStartDate) & "# AND [StartDate] <= #" & CDate(Me!
txtEndDate) & "#"

EnterDate and StartDate are the set up the same in the
table.

Can you see anything here? Somewhere else maybe?

Thanks,
Fred

-----Original Message-----
Hi,


The end result should be:

BETWEEN #9/1/04# AND 1+ #9/13/04#



or (in one line):


strSQL = "[EnterDate] >= #" & CDate(Me!txtStartDate) & "#
AND [EnterDate] < 1+ #" & CDate(Me!txtEndDate) & "#"




Hoping it may help,
Vanderghast, Access MVP

"RedFred" <[email protected]> wrote
Click to expand...
in
message
Thanks, but I am still not getting it.

How does it work without the format force? I'm just an
amateur and there ceratainly won't be any user but my
employees. US, they are.

I thought I understood when the change was "<=" to "<1+";
makes some sense. Don't know coding nearly well enough
though. No combination I tried worked, with or without
the format change.

Have you got more help in ya? Hope!

Thanks.



-----Original Message-----
Hi,



strSQL= " ... AND [EnterDate] < 1+ " & Format
( ..., "\#mm-dd-yyyy\#" )


If you don't force the American format, you may be due
for hard to debug
error if you application is used else where in the world
(including Canada,
UK, Australia, etc).


Hoping it may help,
Vanderghast, Access MVP


message
I see how that works, Fred, but how about when I use it
this way? How do I limit as I do with the
BETWEEN ". . .
23:59:59 PM#"

strSQL = "[EnterDate] >= #" & CDate(Me! txtStartDate)
& "#
AND [EnterDate] <= #" & CDate(Me!txtEndDate) & "#"

I mean, how do I qualify this statement like in the
query?

Thanks.

-----Original Message-----
On Mon, 13 Sep 2004 09:59:43 -0700, RedFred wrote:

Table has fields [EnterDate] and [StartDate]. Each
is
date/time, m/d/yy, indexed dupes ok.

Query 1 retrieves [EnterDate]; query 2 [StartDate].

I limit return by using "BETWEEN #9/1/04# AND
#9/13/04#.

Problem:
Running query 2 returns all records, INcluding
9/13.
Running query 1 returns all records, EXcluding
9/13.

Everything appears the same except for the lack of
9/13
records for query 1. I don't get it.

In hopes that someone can see a flaw...

I thank you for your assitance.
RedFred

If the date field in Query 1 has a time value included
in the field,
then searching for records ending on 9/13/2004 will not
find any past
9/13/2004 00:00:00 AM.

Among your options are to change the search value
Click to expand...
to
1
day later
(9/14/2004), or include a time value #9/13/2004
23:59:59
PM#, or
change the field data to remove the time value from the
data if it's
not otherwise needed. An Update query will do this
painlessly.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top