Need HELP! DATE_SUB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Need HELP! DATE_SUB

Author

Topic

nightkids
Starting Member

1 Post

Posted - 2008-06-10 : 19:25:10

Hi guys, i am using microsft access 2003.

Here is my query:

SELECT [Jada Inventory].Item, [Jada Inventory].Description, [Jada Inventory].[Avail Qty], MAX(PurchaseOrderItems.ReceivedDate) AS [Received Date]
FROM [Jada Inventory] INNER JOIN PurchaseOrderItems ON [Jada Inventory].Item = PurchaseOrderItems.ItemNo
WHERE [Received Date] >=  DATE_SUB(NOW(), INTERVAL 30 DAY) 
GROUP BY [Jada Inventory].Item, [Jada Inventory].Description, [Jada Inventory].[Avail Qty]
ORDER BY [Jada Inventory].Item;

When I tried to save this query and run, it showed this error:

Syntax error (missing operator) in query expression '[Received Date] >= DATE_SUB(NOW(), INTERVAL 30 DAY)'.

May I know what happened to this code? Please help me , thanks a lot.

nheidorn
Starting Member

28 Posts

Posted - 2008-06-11 : 11:10:14

DATE_SUB() is a MySQL function not an Access function. I don't use MySQL myself, but it appears that DATE_SUB() adds or subtracts from a date. If so, you should use the DateAdd() function instead. Also, you can't use an alias like [Received Date] in a WHERE statement.

MAX(PurchaseOrderItems.ReceivedDate) >= DateAdd("d", 30, Date())

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources