Need help with a date querie

[jvonschmittou]

Need help with a date query

Okay, Heres what I'm trying to do. I'm trying to do weekly scans on Access connecting to my database. But I want it to be time specific, I want to scan from 3:00:00 PM to 11:59:00 AM. I'm using a form to select the range and putting the time in the qry. Here's an example of it, >=[Forms]![Form1]![StartDate] & " 6:00:00 AM" And <=[Forms]![Form1]![EndDate] & " 3:15:00 PM" This Qry works great for single days but when I do weekly scans it does the whole week not filtering out the times outside of it.

I added a second part to the qry that will filter the times but in the database it has the date also, I want to exclude that information and just read the time. How would I do that? Here's a example of the time format in the database 8/28/2008 5:18:52 PM

I have been trying to use this, but its not working... Suggestions would be greatly appreciated.
Between #6:00:00 AM# And #3:15:00 PM#

 

[jvonschmittou]

Let me try to explain this better, what I'm trying to do is get that second filter to be a static range for 3:30pm to 11:59pm within that weekly qry. I want to scan for something for the week but only in a certain time range.

The problem I'm running into (and i'm sure its a easy fix) is, the time field has the date in it also, so I want to bypass the date and just focus on the time with the second criteria. What command would I use to look over the date and just focus on the time?

Here's a sample field. 8/28/2008 5:18:52 PM

This is what I was using, but It's wrong and doesn't work (I'm sort of new to access)
Between #6:00:00 AM# And #3:15:00 PM#

 

[ParanoidAndroid]

You need to split criteria into two parts like so:

([datetime]-Int([datetime])>=TimeSerial(6,0,0) And [datetime]-Int([datetime])<=TimeSerial(15,15,0))
And
([datetime]>=[Forms]![Form1]![StartDate] And [datetime]<=[Forms]![Form1]![EndDate])

You will need to replace [datetime] with your fieldname.

The first half selects only records between the times required, the second half selects the dates.


Edit to add:
[datetime]-Int([datetime]) removes the date part of the date field leaving just the time.

 

[jvonschmittou]

I'm getting a error with this string, its saying "ODBC--call failed."
([datetime]-Int([datetime])>=TimeSerial(6,0,0) And [datetime]-Int([datetime])<=TimeSerial(15,15,0))

I changed "datetime" to the field name too...

Note: this one works
([datetime]>=[Forms]![Form1]![StartDate] And [datetime]<=[Forms]![Form1]![EndDate])

 

[khawar]

Download the attached sample

This is what I understood you want to do if I am wrong tell me

 

[jvonschmittou]

That's similar to what I'm trying to do. But I want to keep the time part static in the query and just have the date in the form.

I tried ([trdate]-Int([trdate])>=TimeSerial(6,0,0) And [trdate]-Int([trdate])<=TimeSerial(15,15,0)) and it works on yours, but when I do that to my database (making the correct name changes) i get an error saying "ODBC--call failed".

Note, I'm using access to connect to the database. Is it possible to read the command right from left instead of left to right? because thats why the date qry works and the time doesn't.

 

[khawar]

Upload your sample if possible

 

[jvonschmittou]

My version is not far from yours... The problem is with running "Between" on the time field "([trdate]-Int([trdate])"... It extracts the time fine, but when I run any type of filter command on it I get the ODBC error... Any suggestions? I have tried using " 6:30:00 AM" to " 3:30:00 PM", and the time serials with all the same error results. but when I erase the command it pulls the time fine.

When I pull this table on to a local table, (without any modifications to the datatype) it works fine and filters how it's supposed to. Its just when I filter with the connected SQL table... Is there anything else I can do to pull the time from the date/time table? other than using [trdate]-Int([trdate] ?

 

[raskew]

Hi -

Try TimeValue, e.g:

x = now()
? x
9/17/2008 9:53:30 AM 

? timevalue(x)
9:53:30 AM 

? datevalue(x)
9/17/2008

HTH - Bob