Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Adding calculated date to Between clause

Should be easy, but I'm just too tired of messing with this.  I have a simple query that I need to pull records based off of yesterday at 7am to today at 7am. Hard coding the between clause works fine.

Between #3/16/2011 7:00:00 AM# And #3/17/2011 7:00:00 AM#

However I need to do something like this.

Between date()-1& #7:00:00 AM# And date() & #7:00:00 AM#

This is returning an error.
0
bhieb
Asked:
bhieb
2 Solutions
 
CluskittCommented:
Use Dateadd(d,date(),-1)
0
 
Dale FyeCommented:
This should work,
BETWEEN Date() + #07:00:00# AND (Date() + 1 + #07:00:00#)

although this would include values for 07:00 tomorrow.  I prefer to use:

>= Date() AND < (Date() + 1 + #07:00:00#)
0
 
bhiebAuthor Commented:
But how to I concatenate the time in there. date()-1 works fine to get me to yesterdays date it is the concatenating 7am into it that is the issue.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Rey Obrero (Capricorn1)Commented:
use the "+" sign instead of "&"


Between date()-1 + #7:00:00 AM# And date() + #7:00:00 AM#
0
 
Dale FyeCommented:

Sorry, that should have been:

>= Date() -1 + #07:00:00#  AND < Date() + #07:00:00#
0
 
bhiebAuthor Commented:
Perfect. Dang I should have remembered that + is concatenate from my SQL days. DOH!
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 expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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