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
|
|||
|
|||
Ask for date
I have a situation of asking user to input begin date and end date, but query
keeps give me all previous year. For instance, if I asked for 11/11/2009 to 11/19/2009, it included 11/11/2007 to 11/19/2007, 11/11/2008 to 11/19/2008, and 11/11/2009 to 11/19/2009. In table, I have datetime field in GMT. However, in query I have to convert date and time to local time with this function - convert: dateadd("h",-8/24, [datetime]). I put Between [begin] and [end] in the convert field. If I put the Between [begin] and [end] in the datetime field, it queries correctly. What did I do wrong here? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#2
|
|||
|
|||
Ask for date
It appears that ACCESS is interpreting your user's input dates as strings,
not dates. Either declare the parameters as DateTime type in the query, or format the user's input dates as "formatted dates": PARAMETERS [begin] DateTime, [end] DateTime; SELECT * FROM YourTable WHERE dateadd("h",-8/24, [datetime]) Between [begin] AND [end]; Or SELECT * FROM YourTable WHERE dateadd("h",-8/24, [datetime]) Between Format([begin], "\#mm\/dd\/yyyy\#") AND Format([end], "\#mm\/dd\/yyyy\#"); -- Ken Snell http://www.accessmvp.com/KDSnell/ "PhilT via AccessMonster.com" u33099@uwe wrote in message news:9fa6617340cca@uwe... I have a situation of asking user to input begin date and end date, but query keeps give me all previous year. For instance, if I asked for 11/11/2009 to 11/19/2009, it included 11/11/2007 to 11/19/2007, 11/11/2008 to 11/19/2008, and 11/11/2009 to 11/19/2009. In table, I have datetime field in GMT. However, in query I have to convert date and time to local time with this function - convert: dateadd("h",-8/24, [datetime]). I put Between [begin] and [end] in the convert field. If I put the Between [begin] and [end] in the datetime field, it queries correctly. What did I do wrong here? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#3
|
|||
|
|||
Ask for date
I have to convert date and time to local time with this function - convert:
dateadd("h",-8/24, [datetime]). You do realize that -8/24 equals -1/3 or -.333333333 so you are changing the datetime by .333333 hours. Try using this -- DateValue(DateAdd("h",-8, [datetime])) This removes the time from the DateTime. -- Build a little, test a little. "PhilT via AccessMonster.com" wrote: I have a situation of asking user to input begin date and end date, but query keeps give me all previous year. For instance, if I asked for 11/11/2009 to 11/19/2009, it included 11/11/2007 to 11/19/2007, 11/11/2008 to 11/19/2008, and 11/11/2009 to 11/19/2009. In table, I have datetime field in GMT. However, in query I have to convert date and time to local time with this function - convert: dateadd("h",-8/24, [datetime]). I put Between [begin] and [end] in the convert field. If I put the Between [begin] and [end] in the datetime field, it queries correctly. What did I do wrong here? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 . |
Thread Tools | |
Display Modes | |
|
|