Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date before or after another Date

Posted on 2015-01-07
3
Medium Priority
?
112 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 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 143

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 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