If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with: ...query that does not include specified expression ....as part of an aggregate function...
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")); |
#2
|
|||
|
|||
Help with: ...query that does not include specified expression ...
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")); . |
#3
|
|||
|
|||
Help with: ...query that does not include specified expression ...
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")); . |
#4
|
|||
|
|||
Help with: ...query that does not include specified expression ...
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")); . |
#5
|
|||
|
|||
Help with: ...query that does not include specified expression ...
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")); . |
#6
|
|||
|
|||
Help with: ...query that does not include specified expression ...
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")); . |
#7
|
|||
|
|||
Help with: ...query that does not include specified expression ...
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")); . |
#8
|
|||
|
|||
Help with: ...query that does not include specified expression.... as part of an aggregate function...
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")); |
#9
|
|||
|
|||
Help with: ...query that does not include specified expression.... as part of an aggregate function...
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")); |
#10
|
|||
|
|||
Help with: ...query that does not include specified expression.... as part of an aggregate function...
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")); |
Thread Tools | |
Display Modes | |
|
|