Anyone expert in time function

itHighway · May 3rd, 2005, 04:36 PM

Hello!

I'm having problem in displaying records by time.

Problem:
I want to display all records which are posted before 10:00:00 AM on 3rd March. The problem is, it is displaying all records posted before 10:00:00 PM on 3rd March.

Following is the part of query for time:

FORMAT(EXPIRE_TIME,'LONG TIME') < #"& time() &"#

Database is MS Access and time stored in database is in 24Hrs format.

Any idea..
Any help...

Will really be appriciated

Regards
Zeeshan Ahmed

mat41 · May 3rd, 2005, 06:28 PM

Print your sql statement at run time and post it (sounds like you could solve this by trial and error)

Wind is your friend
Matt

itHighway · May 3rd, 2005, 08:02 PM

Following line was printed at run time:

FORMAT(WDS.EXPIRE_DATE,'SHORT DATE') <= #3/3/2005# AND FORMAT(WDS.EXPIRE_TIME,'LONG TIME') < #6:05:06 PM#

itHighway · May 3rd, 2005, 08:05 PM

There isn't any problem of date. Is only the time. It seems it can't differ between "AM" and "PM"

mat41 · May 3rd, 2005, 08:11 PM

I was looking for the complete sql statement. What is WDS.EXPIRE_DATE? I have never seen this before - sorry I have not been much help.

Wind is your friend
Matt

itHighway · May 3rd, 2005, 09:36 PM

Below is the sql query:

SELECT * FROM DOCUMENTS WHERE FORMAT(EXPIRE_DATE,'SHORT DATE') <= #3/3/2005# AND FORMAT(EXPIRE_TIME,'LONG TIME') < #6:05:06 PM#

mat41 · May 3rd, 2005, 10:38 PM

;;;I want to display all records which are posted before 10:00:00 AM on 3rd March.
Wouldnt the query above give you all records before 6:05:06 PM?

IMO Storing dates and times in the same field is a better practice. What is an exact example of the values held in the 'EXPIRE_DATE' and 'EXPIRE_TIME' fileds.

Wind is your friend
Matt