I am trying to retrieve Stocks records with PurchaseDate in the current month but no records are returned even though I do have Stocks records with PurchaseDate in the current month . Please help! thanks!



SELECT Broker_CYJ.BrokerID, Client_CYJ.ClientID, Stock_CYJ.StockID, Stock_CYJ.Company, Stock_CYJ.PurchasePrice, Stock_CYJ.NoShares, (Stock_CYJ.PurchasePrice*Stock_CYJ.NoShares) AS SubTotal, Stock_CYJ.PurchaseDate
FROM ((Broker_CYJ INNER JOIN Client_CYJ ON Broker_CYJ.BrokerID = Client_CYJ.BrokerID) INNER JOIN Stock_CYJ ON Client_CYJ.ClientID = Stock_CYJ.ClientID) INNER JOIN SaleDetails_CYJ ON Stock_CYJ.StockID = SaleDetails_CYJ.StockID
WHERE (((Month([Stock_CYJ.PurchaseDate]))=Month(Now())))
ORDER BY Client_CYJ.ClientID;

Try Date(), instead of Now().

...
WHERE (((Month([Stock_CYJ.PurchaseDate]))=Month(Date())))

You'll want to check the year value as well:

WHERE (((Month([Stock_CYJ.PurchaseDate]))=Month(Date())) AND ((Year([Stock_CYJ.PurchaseDate]))=Year(Date())))

(I think I have the parens right 8->

thankss timk and mishej.. i tried both ways too but it didn't work. i really can't figure why it can't work.