...cannot use a calculated field in a Where clause...

[Need2Know]

Hello Everyone,

Still stumped with a problem I've been trying to resolve since last
week. I've posted here before and received excellent assistance but
now I'm stumped trying to determine the Max(tbl_hld.[As of Date])
Between qry_txn.[Trade date] And qry_txn.[CloseDate] but I cannot use
a calculated field (qry_txn.[CloseDate]) in a Where clause. I've
posted my SQL below. Any assistance offered is greatly appreciated.
Thank you all in advance.


SELECT qry_TXN.[PORTFOLIO CODE], qry_TXN.[CUSIP OR LOAN #], qry_TXN.
[TRADE DATE],
(SELECT max(tbl_closedates.closedate) FROM tbl_closedates WHERE
tbl_closedates.closedate < qry_txn.[trade date]) AS CloseDate, qry_TXN.
[TIME STAMP],
IIf(IsNull(IIf(IsNull(tbl_hld![cusip or loan #]) And qry_TXN![cusip
or loan #]<>"NOTRANSACTION" And [TRANS TYPE]="Income", "PORTIncome",
IIf(IsNull(tbl_hld![cusip or loan #]) And qry_TXN![cusip or loan #]
<>"NOTRANSACTION" And [TRANS TYPE]="Expense","PORTExpense",[TRANS
TYPE]))),"NOTRANSACTION",
IIf(IsNull(tbl_hld![cusip or loan #]) And qry_TXN![cusip or loan #]
<>"NOTRANSACTION" And [TRANS TYPE]="Income","PORTIncome",
IIf(IsNull(tbl_hld![cusip or loan #]) And qry_TXN![cusip or loan #]
<>"NOTRANSACTION" And [TRANS TYPE]="Expense","PORTExpense",[TRANS
TYPE]))) AS TRANSTYPE,
qry_TXN.[SECURITY CURRENCY], qry_TXN.[CASH BALANCE], qry_TXN.
[QUANTITY (UNITS)], qry_TXN.[ACCRUED INT PUR/SOLD BASE], qry_TXN.
[TOTAL AMOUNT BASE],
qry_TXN.[ACCRUED INT PUR/SOLD LOCAL], qry_TXN.[TOTAL AMOUNT LOCAL],
qry_TXN.[INVESTMENT TYPE], qry_TXN.[TRANS NUMBER], qry_TXN.[RM
NUMBER],
qry_TXN.[BROKER NAME], qry_TXN.[TRANSACTION REMARKS], qry_TXN.[MKT
VALUE BASE], qry_TXN.[MKT VALUE LOCAL], qry_TXN.[TRAN DATE]
FROM qry_TXN LEFT JOIN tbl_HLD ON (qry_TXN.[TRADE DATE] = tbl_HLD.[AS
OF DATE]) AND (qry_TXN.[CUSIP OR LOAN #] = tbl_HLD.[CUSIP OR LOAN #])
AND (qry_TXN.[PORTFOLIO CODE] = tbl_HLD.[PORTFOLIO CODE])
ORDER BY qry_TXN.[PORTFOLIO CODE], qry_TXN.[TRAN DATE];

 

[John Spencer]

You have to redo the subquery in the where clause if you want to use it. You
cannot refer to the alias.

As an alternative you could use the DMAX function, although it would probably
be slower. That expression would look like the following:

DMax("CloseDate","tbl_ClosedDates","ClosedDate<" & Format(qry_txn.[trade
date],"\#yyyy-mm-dd\#"))

SELECT qry_TXN.[PORTFOLIO CODE]
, qry_TXN.[CUSIP OR LOAN #], qry_TXN.[TRADE DATE],
(SELECT max(tbl_closedates.closedate)
FROM tbl_closedates
WHERE tbl_closedates.closedate < qry_txn.[trade date]) AS CloseDate
, qry_TXN.[TIME STAMP]
, If(IsNull(IIf(IsNull(tbl_hld![cusip or loan #])
And qry_TXN![cusip or loan #]<>"NOTRANSACTION"
And [TRANS TYPE]="Income"
, "PORTIncome"
, IIf(IsNull(tbl_hld![cusip or loan #])
And qry_TXN![cusip or loan #]<>"NOTRANSACTION"
And [TRANS TYPE]="Expense"
,"PORTExpense"
,[TRANS TYPE]))),"NOTRANSACTION",
IIf(IsNull(tbl_hld![cusip or loan #])
And qry_TXN![cusip or loan #]<>"NOTRANSACTION"
And [TRANS TYPE]="Income"
,"PORTIncome"
, IIf(IsNull(tbl_hld![cusip or loan #])
And qry_TXN![cusip or loan #]<>"NOTRANSACTION"
And [TRANS TYPE]="Expense"
,"PORTExpense"
,[TRANS TYPE]))) AS TRANSTYPE
, qry_TXN.[SECURITY CURRENCY]
, qry_TXN.[CASH BALANCE]
, qry_TXN.[QUANTITY (UNITS)]
, qry_TXN.[ACCRUED INT PUR/SOLD BASE]
, qry_TXN.[TOTAL AMOUNT BASE]
, qry_TXN.[ACCRUED INT PUR/SOLD LOCAL]
, qry_TXN.[TOTAL AMOUNT LOCAL]
, qry_TXN.[INVESTMENT TYPE]
, qry_TXN.[TRANS NUMBER]
, qry_TXN.[RM NUMBER]
, qry_TXN.[BROKER NAME], qry_TXN.[TRANSACTION REMARKS]
, qry_TXN.[MKT VALUE BASE], qry_TXN.[MKT VALUE LOCAL], qry_TXN.[TRAN DATE]

FROM qry_TXN LEFT JOIN tbl_HLD
ON (qry_TXN.[TRADE DATE] = tbl_HLD.[AS OF DATE])
AND (qry_TXN.[CUSIP OR LOAN #] = tbl_HLD.[CUSIP OR LOAN #])
AND (qry_TXN.[PORTFOLIO CODE] = tbl_HLD.[PORTFOLIO CODE])

WHERE (SELECT max(tbl_closedates.closedate)
FROM tbl_closedates
WHERE tbl_closedates.closedate < qry_txn.[trade date]) > #1/1/2009#

ORDER BY qry_TXN.[PORTFOLIO CODE], qry_TXN.[TRAN DATE];

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

Hello Everyone,

Still stumped with a problem I've been trying to resolve since last
week. I've posted here before and received excellent assistance but
now I'm stumped trying to determine the Max(tbl_hld.[As of Date])
Between qry_txn.[Trade date] And qry_txn.[CloseDate] but I cannot use
a calculated field (qry_txn.[CloseDate]) in a Where clause. I've
posted my SQL below. Any assistance offered is greatly appreciated.
Thank you all in advance.


SELECT qry_TXN.[PORTFOLIO CODE], qry_TXN.[CUSIP OR LOAN #], qry_TXN.
[TRADE DATE],
(SELECT max(tbl_closedates.closedate) FROM tbl_closedates WHERE
tbl_closedates.closedate < qry_txn.[trade date]) AS CloseDate, qry_TXN.
[TIME STAMP],
IIf(IsNull(IIf(IsNull(tbl_hld![cusip or loan #]) And qry_TXN![cusip
or loan #]<>"NOTRANSACTION" And [TRANS TYPE]="Income", "PORTIncome",
IIf(IsNull(tbl_hld![cusip or loan #]) And qry_TXN![cusip or loan #]
<>"NOTRANSACTION" And [TRANS TYPE]="Expense","PORTExpense",[TRANS
TYPE]))),"NOTRANSACTION",
IIf(IsNull(tbl_hld![cusip or loan #]) And qry_TXN![cusip or loan #]
<>"NOTRANSACTION" And [TRANS TYPE]="Income","PORTIncome",
IIf(IsNull(tbl_hld![cusip or loan #]) And qry_TXN![cusip or loan #]
<>"NOTRANSACTION" And [TRANS TYPE]="Expense","PORTExpense",[TRANS
TYPE]))) AS TRANSTYPE,
qry_TXN.[SECURITY CURRENCY], qry_TXN.[CASH BALANCE], qry_TXN.
[QUANTITY (UNITS)], qry_TXN.[ACCRUED INT PUR/SOLD BASE], qry_TXN.
[TOTAL AMOUNT BASE],
qry_TXN.[ACCRUED INT PUR/SOLD LOCAL], qry_TXN.[TOTAL AMOUNT LOCAL],
qry_TXN.[INVESTMENT TYPE], qry_TXN.[TRANS NUMBER], qry_TXN.[RM
NUMBER],
qry_TXN.[BROKER NAME], qry_TXN.[TRANSACTION REMARKS], qry_TXN.[MKT
VALUE BASE], qry_TXN.[MKT VALUE LOCAL], qry_TXN.[TRAN DATE]
FROM qry_TXN LEFT JOIN tbl_HLD ON (qry_TXN.[TRADE DATE] = tbl_HLD.[AS
OF DATE]) AND (qry_TXN.[CUSIP OR LOAN #] = tbl_HLD.[CUSIP OR LOAN #])
AND (qry_TXN.[PORTFOLIO CODE] = tbl_HLD.[PORTFOLIO CODE])
ORDER BY qry_TXN.[PORTFOLIO CODE], qry_TXN.[TRAN DATE];

 

[KARL DEWEY]

You did not say what error message you were getting but you are using an
alias of the same name as a field --
(SELECT max(tbl_closedates.closedate) FROM tbl_closedates WHERE
tbl_closedates.closedate < qry_txn.[trade date]) AS CloseDate,
tbl_closedates.closedate and CloseDate

And I always use an alias for table names inside of subqueries.

If that does not fix it start tearing the query apart a piece at a time
until it does work.