• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

Date before or after another Date

Hi all and thanks in advance, I am having some issues trying to get my code to behave

What I have to do is look up data from a MS Access DB and compare it to today's date.

If the date is in the future, show it, if it is in the past, do NOT show it.

Set Events=oConn.Execute("SELECT * FROM events,locations,codes WHERE event_code=code_ID AND event_location=location_ID AND event_live=YES AND event_finish>"&Date()&" ORDER BY event_start")

Open in new window


Any help will be much appreciated

Cheers
0
Graeme
Asked:
Graeme
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try

Set Events=oConn.Execute("SELECT * FROM events,locations,codes WHERE event_code=code_ID AND event_location=location_ID AND event_live=YES AND event_finish>now() ORDER BY event_start")

Open in new window

0
 
Graemewebber4technologiesAuthor Commented:
Thank you very much!! :)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you just use the date function in the SQL directly:
Set Events=oConn.Execute("SELECT * FROM events,locations,codes WHERE event_code=code_ID AND event_location=location_ID AND event_live=YES AND event_finish> Date()  ORDER BY event_start")
                                 
the issue with the code you posted is what is also described here:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
though not specifically for ms access, the issue is that you convert the date value into a string (the sql string), and inside that, it's not working correctly as it will not be recognized as a date, necessarily.
at least, you would need to use # # around the value (if the db behind is really ms access)

please clarify
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now