[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 122
  • 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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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