Between [First Date] And [Last date]

[Peter]

Hi all..i have this issue in a newly formated field (date) =Now()..running a
query with parameters between and..does not give me correct results..any
odeas how to cure this issue?

Thanks!

 

[kc-mass]

My guess is that the problem is using "Now()" for the data.
Now gives you both the date and the time.
Try using "Date()" which only supplies the Date instead
and your between function should work.

Regards

Kevin

 

[John Spencer]

If you have used now then you probably need to use the following

WHERE [someDate] >= [First Date] AND [someDate] < DateAdd("d",1,[Last Date])

Your problem is that SomeDate field contains a time in addition to the date.
So Feb 28 2010 at 11:00:00 is not between Feb 1, 2010 00:00:00 and Feb 28,
2010 00:00:00.

You could enter the date plus a time in response to the Last Date prompt and
then use Between ... And ... with little problem. Something like the
following would work for 99.999 percent of the cases. It is possible (very
rare) to get a date and time that is between 23:59:59 and 24:00:00.

BETWEEN #2009-02-01# and #2009-02-28 23:59:59#

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Peter]

Hiand thanks, but i am a lite confused. I use the Between [First Date] and
[Last Date) in a parameter/criteria that results in two small popup windows
where the user populate the frst date and second in order to retrive
allrecords between these two dates...but i dont understand how that would
function in your solution..all i want to do is to retrieve records between
two dates...The datecreated field is populated by code and it seems that i
can not just reformat the field to ex dd-mm-yy..?

If you have used now then you probably need to use the following

WHERE [someDate] >= [First Date] AND [someDate] < DateAdd("d",1,[Last Date])

Your problem is that SomeDate field contains a time in addition to the date.
So Feb 28 2010 at 11:00:00 is not between Feb 1, 2010 00:00:00 and Feb 28,
2010 00:00:00.

You could enter the date plus a time in response to the Last Date prompt and
then use Between ... And ... with little problem. Something like the
following would work for 99.999 percent of the cases. It is possible (very
rare) to get a date and time that is between 23:59:59 and 24:00:00.

BETWEEN #2009-02-01# and #2009-02-28 23:59:59#

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi all..i have this issue in a newly formated field (date) =Now()..running a
query with parameters between and..does not give me correct results..any
odeas how to cure this issue?

Thanks!

.

 

[kc-mass]

Peter,
The real solution is to populate the field in your table with the just the
date.
Failing that, in your query write an expression that converts the data
before use.
On the field line enter something like:

JustTheDate: Year(MyDatefld) & "-" &Month(MyDatefld) &"-" & Day(MyDatefld)

Set your parameters against that and it will work

Regards

Kevin

Hiand thanks, but i am a lite confused. I use the Between [First Date] and
[Last Date) in a parameter/criteria that results in two small popup
windows
where the user populate the frst date and second in order to retrive
allrecords between these two dates...but i dont understand how that would
function in your solution..all i want to do is to retrieve records between
two dates...The datecreated field is populated by code and it seems that i
can not just reformat the field to ex dd-mm-yy..?

If you have used now then you probably need to use the following

WHERE [someDate] >= [First Date] AND [someDate] < DateAdd("d",1,[Last
Date])

Your problem is that SomeDate field contains a time in addition to the
date.
So Feb 28 2010 at 11:00:00 is not between Feb 1, 2010 00:00:00 and Feb
28,
2010 00:00:00.

You could enter the date plus a time in response to the Last Date prompt
and
then use Between ... And ... with little problem. Something like the
following would work for 99.999 percent of the cases. It is possible
(very
rare) to get a date and time that is between 23:59:59 and 24:00:00.

BETWEEN #2009-02-01# and #2009-02-28 23:59:59#

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi all..i have this issue in a newly formated field (date)
=Now()..running a
query with parameters between and..does not give me correct
results..any
odeas how to cure this issue?

Thanks!

.

 

[Duane Hookom]

I would not convert the date to a string for comparision like this. Consider
reading through Allen Browne's information on international date formats
http://www.allenbrowne.com/ser-36.html.

--
Duane Hookom
Microsoft Access MVP

Peter,
The real solution is to populate the field in your table with the just the
date.
Failing that, in your query write an expression that converts the data
before use.
On the field line enter something like:

JustTheDate: Year(MyDatefld) & "-" &Month(MyDatefld) &"-" & Day(MyDatefld)

Set your parameters against that and it will work

Regards

Kevin

Hiand thanks, but i am a lite confused. I use the Between [First Date] and
[Last Date) in a parameter/criteria that results in two small popup
windows
where the user populate the frst date and second in order to retrive
allrecords between these two dates...but i dont understand how that would
function in your solution..all i want to do is to retrieve records between
two dates...The datecreated field is populated by code and it seems that i
can not just reformat the field to ex dd-mm-yy..?

If you have used now then you probably need to use the following

WHERE [someDate] >= [First Date] AND [someDate] < DateAdd("d",1,[Last
Date])

Your problem is that SomeDate field contains a time in addition to the
date.
So Feb 28 2010 at 11:00:00 is not between Feb 1, 2010 00:00:00 and Feb
28,
2010 00:00:00.

You could enter the date plus a time in response to the Last Date prompt
and
then use Between ... And ... with little problem. Something like the
following would work for 99.999 percent of the cases. It is possible
(very
rare) to get a date and time that is between 23:59:59 and 24:00:00.

BETWEEN #2009-02-01# and #2009-02-28 23:59:59#

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Peter wrote:
Hi all..i have this issue in a newly formated field (date)
=Now()..running a
query with parameters between and..does not give me correct
results..any
odeas how to cure this issue?

Thanks!
.

.

 

[Peter]

Nope..it does not get the job done..and Datevalue does not function either..i
made a mistake in recording date and time in the same field.....

I would not convert the date to a string for comparision like this. Consider
reading through Allen Browne's information on international date formats
http://www.allenbrowne.com/ser-36.html.

--
Duane Hookom
Microsoft Access MVP

Peter,
The real solution is to populate the field in your table with the just the
date.
Failing that, in your query write an expression that converts the data
before use.
On the field line enter something like:

JustTheDate: Year(MyDatefld) & "-" &Month(MyDatefld) &"-" & Day(MyDatefld)

Set your parameters against that and it will work

Regards

Kevin

Hiand thanks, but i am a lite confused. I use the Between [First Date] and
[Last Date) in a parameter/criteria that results in two small popup
windows
where the user populate the frst date and second in order to retrive
allrecords between these two dates...but i dont understand how that would
function in your solution..all i want to do is to retrieve records between
two dates...The datecreated field is populated by code and it seems that i
can not just reformat the field to ex dd-mm-yy..?

:

If you have used now then you probably need to use the following

WHERE [someDate] >= [First Date] AND [someDate] < DateAdd("d",1,[Last
Date])

Your problem is that SomeDate field contains a time in addition to the
date.
So Feb 28 2010 at 11:00:00 is not between Feb 1, 2010 00:00:00 and Feb
28,
2010 00:00:00.

You could enter the date plus a time in response to the Last Date prompt
and
then use Between ... And ... with little problem. Something like the
following would work for 99.999 percent of the cases. It is possible
(very
rare) to get a date and time that is between 23:59:59 and 24:00:00.

BETWEEN #2009-02-01# and #2009-02-28 23:59:59#

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Peter wrote:
Hi all..i have this issue in a newly formated field (date)
=Now()..running a
query with parameters between and..does not give me correct
results..any
odeas how to cure this issue?

Thanks!
.

.

 

[Rick Brandt]

Nope..it does not get the job done..and Datevalue does not function
either..i made a mistake in recording date and time in the same field.....

No, that is the correct thing to do. Just write your query properly.

BETWEEN is just not well-suited to DateTime fields. Instead use >= on the
starting date and < on the ending date after adding a day to it. It's dead
simple and absolutely solves all the problems.

 

[John Spencer]

Try using the following criteria

BETWEEN [First Date] and DateAdd("s",23599,[Last Date])

The DateAdd function adds 23599 seconds to the value of Last Date so that the
datetime value is one second before midnight on the date you enter for Last
Date. So you will get all records between midnight of first date and one
second before midnight on the last date.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Dang!! Wrong constant there are 86400 seconds in a day not 24000.

BETWEEN [First Date] and DateAdd("s",86399,[Last Date])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Try using the following criteria

BETWEEN [First Date] and DateAdd("s",23599,[Last Date])

The DateAdd function adds 23599 seconds to the value of Last Date so
that the datetime value is one second before midnight on the date you
enter for Last Date. So you will get all records between midnight of
first date and one second before midnight on the last date.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hiand thanks, but i am a lite confused. I use the Between [First Date]
and [Last Date) in a parameter/criteria that results in two small
popup windows where the user populate the frst date and second in
order to retrive allrecords between these two dates...but i dont
understand how that would function in your solution..all i want to do
is to retrieve records between two dates...The datecreated field is
populated by code and it seems that i can not just reformat the field
to ex dd-mm-yy.

 

[Peter]

John, Rick,Duane,kc-mass...I am much obliged. Thank you indeed...Looking at
all these suggestions..and testing..it is simple....thats why it is
difficult.. It works when adding a day in seconds or not using the Between
but operators..

Once again, Thank you for the lesson...i learned something new today..

Dang!! Wrong constant there are 86400 seconds in a day not 24000.

BETWEEN [First Date] and DateAdd("s",86399,[Last Date])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Try using the following criteria

BETWEEN [First Date] and DateAdd("s",23599,[Last Date])

The DateAdd function adds 23599 seconds to the value of Last Date so
that the datetime value is one second before midnight on the date you
enter for Last Date. So you will get all records between midnight of
first date and one second before midnight on the last date.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hiand thanks, but i am a lite confused. I use the Between [First Date]
and [Last Date) in a parameter/criteria that results in two small
popup windows where the user populate the frst date and second in
order to retrive allrecords between these two dates...but i dont
understand how that would function in your solution..all i want to do
is to retrieve records between two dates...The datecreated field is
populated by code and it seems that i can not just reformat the field
to ex dd-mm-yy.

.