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
Solved

Date before or after another Date

Posted on 2015-01-07
3
102 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

839 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