Hi, i am using a SQL statement in order to generate a report of the products
that have sold. I can do this with no problem. But how do i amend the SQL
statement to show the products that have been sold in the day, week or month.
Basically, i want the report to show the products which products have been
sold

The following is the SQL statement currenctly being used:

SELECT Product.ProductID, Product.ProductName, OrderItem.Quantity,
Orders.`Date` FROM OrderItem, Product, Orders WHERE OrderItem.ProductID =
Product.ProductID AND OrderItem.OrderID = Orders.OrderID ORDER BY
Product.ProductName

regards

Show quote Hide quote Include your "Date" column in your WHERE clause and specify an appropriate
condition.  I'd recommend that you make your query an INNER JOIN.

SELECT Product.ProductID, Product.ProductName, OrderItem.Quantity,
Orders.`Date`
FROM OrderItem
INNER JOIN Product ON OrderItem.ProductID = Product.ProductID
INNER JOIN Orders ON OrderItem.OrderID = Orders.OrderID
WHERE Orders.'Date' = '20060305'
ORDER BY Product.ProductName

If you must use a literal date value, then make sure you format and/or
delimit the date value properly.  In the above, I used SQL Server's standard
internal date format (YYYYMMDD), which will always work as expected in SQL
Server (IOW, no ambiguity caused by the date format).  However, exactly how
you do this depends on your DB engine.  If you can, it's best to avoid
literal date values.  For example, with SQL Server, if you want all products
sold for the current month, do something like this:

WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') = MONTH
(GETDATE())

For all products sold last month:

WHERE YEAR(Orders.'Date') = YEAR(GETDATE()) AND MONTH(Orders.'Date') = MONTH
(GETDATE()) -1

For all products sold last year:

WHERE YEAR(Orders.'Date') =  YEAR(GETDATE()) -1

Again, for other DB engines, this may be different.  I don't think Access
has or recognizes an internal GETDATE function, so you'd have to go about it
differently. I also recommend renaming your Date column to something else,
perhaps DateSold.

BTW, this question is really more appropriate for a SQL newsgroup of the DB
engine you're using.  It doesn't have anything to do with VB.

Resource icon issue
default property of class
What kind of value is that...?
Math problem
vb5 & Access 2003
Find Path from VB
Problem converting bytes array into string
Predictive dialer
Keyboard hook callback executing twice?
Find Path from VB