Solved

Microsoft Access Query

Posted on 2014-10-30
4
157 Views
Last Modified: 2014-11-04
Hi,

I am looking for help with an access query,
I am looking for the date parameter to be the system date -1 day

My current query is

SELECT Products.ItemNumber, Products.ItemName, Sum(SalesHistory.Revenue) AS SumOfRevenue, Sum(SalesHistory.NumberSold) AS SumOfNumberSold
FROM Products INNER JOIN SalesHistory ON Products.ItemNumber = SalesHistory.ItemNumber
WHERE (((SalesHistory.ImportDate)=Date()))
GROUP BY Products.ItemNumber, Products.ItemName
ORDER BY Sum(SalesHistory.NumberSold) DESC;
0
Comment
Question by:hellblazeruk
4 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40412882
IF ImportDate is purely a date field (no time component) then

WHERE (((SalesHistory.ImportDate)=Date() -1))

Otherwise, you will need something like:

WHERE (SalesHistory.ImportDate>=Date() -1)
AND (SalesHistory.ImportDate < Date())
0
 
LVL 1

Expert Comment

by:mfwiniberg
ID: 40412883
In T_SQL you would use:

DATEADD(day,-1,GETDATE())

For JET (Access):

Dateadd("d",-1,Date())
0
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 40413960
I would tend towards BETWEEN:

WHERE SalesHistory.ImportDate BETWEEN Date()-1 AND Date()
0
 

Author Closing Comment

by:hellblazeruk
ID: 40421999
Thank you.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now