If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Between [First Date] And [Last date]
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! |
#2
|
|||
|
|||
Between [First Date] And [Last date]
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 "Peter" wrote in message ... 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! |
#3
|
|||
|
|||
Between [First Date] And [Last date]
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! |
#4
|
|||
|
|||
Between [First Date] And [Last date]
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..? "John Spencer" wrote: 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! . |
#5
|
|||
|
|||
Between [First Date] And [Last date]
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 "Peter" wrote in message ... 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..? "John Spencer" wrote: 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! . |
#6
|
|||
|
|||
Between [First Date] And [Last date]
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 "kc-mass" wrote: 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 "Peter" wrote in message ... 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..? "John Spencer" wrote: 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! . . |
#7
|
|||
|
|||
Between [First Date] And [Last date]
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..... "Duane Hookom" wrote: 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 "kc-mass" wrote: 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 "Peter" wrote in message ... 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..? "John Spencer" wrote: 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! . . |
#8
|
|||
|
|||
Between [First Date] And [Last date]
Peter wrote:
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. |
#9
|
|||
|
|||
Between [First Date] And [Last date]
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 Peter wrote: 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. |
#10
|
|||
|
|||
Between [First Date] And [Last date]
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 John Spencer wrote: 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 Peter wrote: 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. |
|
Thread Tools | |
Display Modes | |
|
|