Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    28

    Unanswered: Help with IIF Function

    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

    ((Table1.DCD)="UNMAPPED"))
    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.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2009
    Posts
    28
    Thanks for the quick response Teddy. I actually changed the table names and forgot to update that one. The IIF statement is

    IIf((Table1.MCC = Table2.MCC),Table2.Arrival_Date," ") AS Arrival_Date

    I am trying to pull the dates from a previous weeks table based on the MCC field.

    Also the table2 has 20 rows or less, not sure if it helps.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Let me state this again, clearly:

    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&#37; of the time anyway.


    If you'd like more help on your error message, please include the ENTIRE error message.
    Last edited by Teddy; 12-22-09 at 18:00.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jul 2009
    Posts
    32
    I think what kvkanuri intended a LEFT JOIN instead of INNER JOIN.

    Not sure if this is the problem, but offhand I noticed that you have too many opening brackets in:

    Code:
    HAVING (((Sum(Table1.Amount))>10000000 Or (Sum(Table1.Amount))<-10000000) AND
    
    ((Table1.DCD)="UNMAPPED"))
    I'd probably rewrite that as:

    Code:
    HAVING ((Sum(Table1.Amount)>10000000) Or (Sum(Table1.Amount)<-10000000)) AND (Table1.DCD="UNMAPPED")
    depending on whether the first two conditions are meant to be read together, you may even omit one more pair of brackets there.
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •