Solved

Date before or after another Date

Posted on 2015-01-07
3
96 Views
Last Modified: 2015-01-07
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
Comment
Question by:Graeme
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40535429
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
 

Author Closing Comment

by:Graeme
ID: 40535433
Thank you very much!! :)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40535435
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now