• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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