Solved

Adding calculated date to Between clause

Posted on 2011-03-17
6
183 Views
Last Modified: 2012-05-11
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
Comment
Question by:bhieb
[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
6 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35156822
Use Dateadd(d,date(),-1)
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 35156834
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
 

Author Comment

by:bhieb
ID: 35156837
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 35156840
use the "+" sign instead of "&"


Between date()-1 + #7:00:00 AM# And date() + #7:00:00 AM#
0
 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 35156858

Sorry, that should have been:

>= Date() -1 + #07:00:00#  AND < Date() + #07:00:00#
0
 

Author Comment

by:bhieb
ID: 35156879
Perfect. Dang I should have remembered that + is concatenate from my SQL days. DOH!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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.
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…

707 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