Help with: ...query that does not include specified expression ....as part of an aggregate function.

[Need2Know]

Good Afternoon,

I keep getting "You tried to execute a query that does not include the
specified expression '(Max(.[AS OF DATE])) Between [a].[closedate]
And [a].[trade date]' as part of an aggregate function." when runnng
the query below. Any assistance offered is greatly appreciated.
Thank you.

UPDATE DISTINCTROW tbl_TXN_PortIncExp AS a LEFT JOIN tbl_HLD AS b ON
(a.[CUSIP OR LOAN #] = b.[CUSIP OR LOAN #]) AND (a.[PORTFOLIO CODE] =
b.[PORTFOLIO CODE])
SET a.XDate = (Max(.[AS OF DATE])) Between [a].[closedate] And [a].
[trade date], a.TRANSTYPE = "Income"
WHERE (((a.TRANSTYPE)="portincome"));

 

[Dorian]

Your SQL syntax if invalid.
To use MAX() you need a SELECT clause and a FROM clause.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[KARL DEWEY]

Whenever you use Totals functions you must GROUP BY all else in the query.

But you have another problem --
SET a.XDate = (Max(.[AS OF DATE])) Between [a].[closedate] And [a].[trade
date],
You can not set a.XDate to two different dates.

Try removing -- Between [a].[closedate] And [a].[trade date],

 

[John Spencer]

That query looks like a mess. First thing is you cannot use Max or any other
SQL aggregate function in the SET clause of an update query. You can use the
VBA DMAX function.

PERHAPS something like the following would work for you. Backup your data
BEFORE you try this so that you can recover if something goes wrong.

UPDATE tbl_TXN_PortIncExp AS A
SET a.TransType = "Income"
, a.XDate =
DMax("[As of date]","tbl_hld",[As Of Date] between #" & CloseDate "# and #" &
[Trade Date & "# And [CUSIP OR LOAN #] =""" & [CUSIP OR LOAN #] & """ AND
[PORTFOLIO CODE]=""" & [PORTFOLIO CODE] & """")
WHERE a.TRANSTYPE="portincome"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County