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,472
    Provided Answers: 10
    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

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  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: 15
    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,764
    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
  •