Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2007
    Posts
    127

    Unanswered: IIf statement - Equals

    I can’t get my IIf statement to compile…

    If a field (ConsultantCode) from table1 (IPContact) matches a field (Consultant_ID) in table2 (Consultant), I want it to populate a field (ReferringConsultant) from the data (Surname) in table 2 (Consultant), otherwise leave it blank.

    This is what I have created… it doesn’t work, I’m sure I’m not writing it correctly..

    ReferringConsultant:IIf(IPContact!ConsultantCode] = [Consultant!Consultant_ID], [Consultant!Surname],"")

    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    whee is this being used
    in a query? in the query designer or in the SQL panel?
    in a form?
    in a report?
    in a macro?

  3. #3
    Join Date
    Oct 2007
    Posts
    127
    Quote Originally Posted by healdem
    whee is this being used
    in a query? in the query designer or in the SQL panel?
    in a form?
    in a report?
    in a macro?
    in a query mate...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you show the query please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2007
    Posts
    127
    Quote Originally Posted by r937
    could you show the query please?
    ReferringConsultant:IIf(IPContact!ConsultantCode] = [Consultant!Consultant_ID], [Consultant!Surname],"")

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, what i meant was, go to the query's SQL view, and copy the statement from there (it's an UPDATE, right?)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Your square brackets are screwed up. Should be [table]![column] not [table!column].

  8. #8
    Join Date
    Oct 2007
    Posts
    127
    SELECT IIf([IPContact]![ConsultantCode]=[Consultant]![Consultant_ID],[Consultant]![Surname],"") AS ReferringConsultant INTO new
    FROM Consultant INNER JOIN AR_20071107_tbl_YvonnePettigrew_TherapyMilitary01 ON Consultant.CONSULTANT_ID = AR_20071107_tbl_YvonnePettigrew_TherapyMilitary01. ReferringAgent;

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Where is the table ipcontact defined in this query?

    Code:
    SELECT IIf([IPContact]![ConsultantCode]=[Consultant]![Consultant_ID],[Consultant]![Surname],"") AS ReferringConsultant INTO new
    FROM Consultant INNER JOIN AR_20071107_tbl_YvonnePettigrew_TherapyMilitary01 ON Consultant.CONSULTANT_ID = AR_20071107_tbl_YvonnePettigrew_TherapyMilitary01. ReferringAgent;
    could you get the same effect without using a IIF statement using a left join
    Code:
    SELECT Consultant.Surname AS ReferringConsultant INTO new
    FROM Consultant INNER JOIN AR_20071107_tbl_YvonnePettigrew_TherapyMilitary01 ON Consultant.CONSULTANT_ID = AR_20071107_tbl_YvonnePettigrew_TherapyMilitary01. ReferringAgent
    left join ipcontact on ipcontact.consultantcode = consultant.consultantid;

  10. #10
    Join Date
    Oct 2007
    Posts
    127
    thanks mate.. you a star

Posting Permissions

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