I am trying to write the following query in Access, but I keep getting the warning "Not part of the expression" Can you please let me know what is wrong
SELECT IIf((Table1.MCC = Table2.MCC),Pre_Table.Arrival_Date," ") AS Arrival_Date, Table1.ASN, Table1.AMN, Table1.MCC, Sum(Table1.Amount) AS [Amount]
FROM Table1 INNER JOIN Table2 ON Table1.MCC = Table2.MCC
GROUP BY Table1.ASN, Table1.AMN, Table1.MCC, Table1.DCD
HAVING (((Sum(Table1.Amount))>10000000 Or (Sum(Table1.Amount))<-10000000) AND
ORDER BY Abs(Sum(Table1.Amount)) DESC;
I am trying to pull the output as Table2.Arrival_Date based on the condition highlighted. Not sure if the syntax is valid.
I don't see exactly what would cause that error. However, it's pointless to bother with that IIF() statement since you're grouping on Table1.MCC = Table2.MCC. It is physically impossible to return rows where Table1.MCC and Table2.MCC will NOT be equivilent given that join criteria.
Edit: I noticed your prefix for the "true" condition references a table called Pre_Table. Pre_table is not part of your FROM clause and may be the source of that error.
Table1.MCC will ALWAYS be the same as Table2.MCC. Always. It will never, ever, ever evaluate to false. The result of your iif() statement will never be your single space. It will always be Table2.Arrival_Date.
You might as well check to see if 1=1.
Therefore I don't understand why you don't directly reference Arrival_Date since that's what you're going to get 100% of the time anyway.
If you'd like more help on your error message, please include the ENTIRE error message.