Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1

    Answered: IIF expression not working

    Ok, this is making me crazy and I can't figure out what is wrong. I'm putting the teacher's and/or aide's name in a report. Some classes have a teacher and an aide, some just a teacher, and some neither (homeschool). This is what I have right now:

    =IIf([Aide] Is Null,DLookUp("[Full_Name]","[Teachers]","[Teacher_ID]=" & [Teacher]),IIf([Teacher] Is Null,0,DLookUp("[Full_Name]","[Teachers]","[Teacher_ID]=" & [Teacher])+" / "+DLookUp("[Full_Name]","[Teachers]","[Teacher_ID]=" & [Aide])))

    It is the IIf([Teacher] Is Null,0 part that is causing me problems. Without it, the expression works fine except for classes without a teacher. And that bit of expression works if I put it in a new box and add [Teacher] for the False part. But when I put the two bits together, I'm still getting an error for classes with no teacher.

    So what am I doing wrong? Thanks!

  2. Best Answer
    Posted by KarenElissa

    "Ok, figured it out, it is really unhelpful to have ideas while I'm falling asleep on Thursday when I won't be back at work til Tuesday to see if they work.

    But I had to have the teacher part first, it checks stuff in order of the query right? So it is:

    =IIf([Teacher] Is Null,"",IIf([Aide] Is Null,DLookUp("[Full_Name]","[Teachers]","[Teacher_ID]=" & [Teacher]),DLookUp("[Full_Name]","[Teachers]","[Teacher_ID]=" & [Teacher])+" / "+DLookUp("[Full_Name]","[Teachers]","[Teacher_ID]=" & [Aide])))

    And that works perfectly! "


  3. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I think its the Is null should it be IsNull()

    also I would put into a function them call it from the Query

    Function Who(aideID,TeacherID)
    'the code
    End Function

    save the module


    then in the query Who:Who([Aide],[Teacher_ID])
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #3
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    Adding the () definitely doesn't help, that shoots back an error. And sorry, I don't understand the second half of what you said. I have a bit of Access experience/coding knowledge, but mostly I'm just learning as I go.

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    could you not do this via a query
    ..is this being used in a form or report
    is it being used as a control dataource?

    what is stopping you doing a left join to the teachers table (twice), one for the aide, once for the teacher
    ... if it is from a query can we see the actual query
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As myle said, you must use the VBA function IsNull([Expression]) and not the SQL [Expression] IS NULL operator when working with the IIf() function.
    Have a nice day!

  7. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In other words, instead of

    IIf([Teacher] Is Null,0

    you need

    IIf(IsNull([Teacher]),0

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #7
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    Ok, figured it out, it is really unhelpful to have ideas while I'm falling asleep on Thursday when I won't be back at work til Tuesday to see if they work.

    But I had to have the teacher part first, it checks stuff in order of the query right? So it is:

    =IIf([Teacher] Is Null,"",IIf([Aide] Is Null,DLookUp("[Full_Name]","[Teachers]","[Teacher_ID]=" & [Teacher]),DLookUp("[Full_Name]","[Teachers]","[Teacher_ID]=" & [Teacher])+" / "+DLookUp("[Full_Name]","[Teachers]","[Teacher_ID]=" & [Aide])))

    And that works perfectly!

Posting Permissions

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