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.
bhiebAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
use the "+" sign instead of "&"


Between date()-1 + #7:00:00 AM# And date() + #7:00:00 AM#
0
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
 
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.

All Courses

From novice to tech pro — start learning today.