Solved

Access 2007 SQL View Where Clause Help

Posted on 2014-04-02
5
514 Views
Last Modified: 2014-04-02
I need to correct the below Where clause so (access 2007 sql view) it finds all records in table
dbo_dAgentPerformanceStat that have a date 1 day less than todays date (if todays is 04/02/2014).

Right now the below WHERE code is not finding match since the Timestamp is  
2014-04-01 00:00:00  and DateSerial is calculating date as 04/01/2014.



WHERE (((dbo_dAgentPerformanceStat.Timestamp)=DateSerial(Year(Now()),Month(Now()),Day(Now()))-"1"));

dbo_dAgentPerformanceStat.Timestamp = 2014-04-01 00:00:00
DateSerial(Year(Now()),Month(Now()),Day(Now()))-"1")) = 04/01/2014


How would you create a Where clause that will compare the correct way so I find matches ?
0
Comment
Question by:thayduck
[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
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39973282
why not use dateserial (or better still, datevalue) on both dates (after the dateadd part)?
0
 
LVL 5

Assisted Solution

by:Steve Dubyo
Steve Dubyo earned 25 total points
ID: 39973306
Hi,

You could use the Format function to change the date value to match your Timestamp column like this...

WHERE (dbo_dAgentPerformanceStat.Timestamp)=(Format(DateAdd("d",-1,Now()),"yyyy-mm-dd")+" 00:00:00");

Open in new window

0
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 75 total points
ID: 39973345
using dateadd and datevalue is good.

WHERE (Datevalue(dbo_dAgentPerformanceStat.Timestamp))=DateValue(Dateadd('d',-1,Now()));
0
 

Author Closing Comment

by:thayduck
ID: 39973381
Both solutions work and solve my problem.

Like COACHMAN99 a little better because I will not have to worry about TIMES if they happen to start showing up since I only have read access over this table.

Thanks for quick responses......
0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 39973385
Agree that the DateValue approach is actually cleaner.  Good luck.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

710 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