Query on Date n Time Field.

[kbsudhir]

Hi All,

I am using Access 2003. I have a table with "DateTime" Field.

This field captures the received date n Time for teh incoming mails in by outlook in the belwo format:

"6/29/2009 3:30:12 PM".

Now I want to create a query to pull the deadline from this column. It pull those records which have date of today & time is for after "4Hrs"

Ex. if its 4:00AM in then it pull data for todays date till the time is "<=8:00AM".


Please guide.

Any assitance is appreciated.

Regards

[OBP]

Split the Date?Time field in to 2 Columns one with date and one with Time formats. Compare the date column to date() in the Criteria Row and the Time to
Between 04:00 and 08:00

[kbsudhir]

Thanks OBP but how to split them...?????

Also I can't use the static time I have to get the data which is between the current time & four hours after the current time.

[kbsudhir]

Well, now I am able to split the using format([fieldname],"Long Time") & similarly format([fieldname],"short date") .

But still Iam not able to enter the criteria which can provide the data between current time & 4Hours from the current time.

Please guide

[OBP]

Between Time() and Time()+#04:00:00#

[kbsudhir]

I used the same but system is converting "Time()+#04:00:00# to "Time()+"04:00:00 AM",

Please guide.

Regards
Sudhir

[OBP]

Sudhir, it worked for me in Access 2002.
Does it not work with that conversion as well?

[kbsudhir]

Nope, its not working in Access 2003.

[OBP]

Well I am not sure what else to suggest, can you post a copy of the database?

[hansup]

I used the same but system is converting "Time()+#04:00:00# to "Time()+"04:00:00 AM",
In Access, "time" is a fraction of a day (24 hours). See if you can get it to work this way:

Between Time() and Time() + (4/24)

[kbsudhir]

Thanks Hansup & OBP.

I will try this out tomorrow when I log into my office.
Also try to update a copy of database.