A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Ask for date



 
 
Thread Tools Display Modes
  #1  
Old November 25th, 2009, 05:25 PM posted to microsoft.public.access.queries
PhilT via AccessMonster.com
external usenet poster
 
Posts: 22
Default 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  
Old November 25th, 2009, 06:07 PM posted to microsoft.public.access.queries
Ken Snell
external usenet poster
 
Posts: 177
Default 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  
Old November 25th, 2009, 06:13 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.