Using Subqueries within Update Queries

[MGalyardt]

What is the syntax for creating an update query with the new information as a
subquery calculation. This is what I have in the SQL statement, but an error
message keeps coming up:

UPDATE DISTINCTROW [Commuter Sales] SET [Commuter Sales].[Dena Daily Sales]
= (SELECT DISTINCTROW Count(*) AS [Daily Sales]
FROM [Commuter]
WHERE ((([Commuter].[Purchase Date])=Date()) AND
(([Commuter].[Cashier])="DENA")));

 

[Steve Schapel]

MGalyardt,

You could try it like this...
UPDATE [Commuter Sales] SET [Commuter Sales].[Dena Daily Sales]
= DCount("*","Commuter","[Purchase Date]=Date() AND [Cashier]='DENA'")