Good Afternoon,

I keep getting "You tried to execute a query that does not include the
specified expression '(Max([b].[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([b].[AS OF DATE])) Between [a].[closedate] And [a].
[trade date], a.TRANSTYPE = "Income"
WHERE (((a.TRANSTYPE)="portincome"));

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".


"Need2Know" wrote:

> Good Afternoon,
> 
> I keep getting "You tried to execute a query that does not include the
> specified expression '(Max([b].[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([b].[AS OF DATE])) Between [a].[closedate] And [a].
> [trade date], a.TRANSTYPE = "Income"
> WHERE (((a.TRANSTYPE)="portincome"));
> .
> 

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

But you have another problem --
SET a.XDate = (Max([b].[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], 

-- 
Build a little, test a little.


"Need2Know" wrote:

> Good Afternoon,
> 
> I keep getting "You tried to execute a query that does not include the
> specified expression '(Max([b].[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([b].[AS OF DATE])) Between [a].[closedate] And [a].
> [trade date], a.TRANSTYPE = "Income"
> WHERE (((a.TRANSTYPE)="portincome"));
> .
> 

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

Need2Know wrote:
> Good Afternoon,
> 
> I keep getting "You tried to execute a query that does not include the
> specified expression '(Max([b].[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([b].[AS OF DATE])) Between [a].[closedate] And [a].
> [trade date], a.TRANSTYPE = "Income"
> WHERE (((a.TRANSTYPE)="portincome"));