Solved

Microsoft Access Query

Posted on 2014-10-30
4
160 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

730 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