Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

Microsoft Access Query

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
hellblazeruk
Asked:
hellblazeruk
1 Solution
 
Dale FyeCommented:
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
 
mfwinibergCommented:
In T_SQL you would use:

DATEADD(day,-1,GETDATE())

For JET (Access):

Dateadd("d",-1,Date())
0
 
SteveCommented:
I would tend towards BETWEEN:

WHERE SalesHistory.ImportDate BETWEEN Date()-1 AND Date()
0
 
hellblazerukAuthor Commented:
Thank you.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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