Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    Unanswered: iif function in criteria problems

    Hello all,
    I'm having a problem with the iif function in the query criteria and I'm hoping I can find some help here. What I am trying to do is take the criteria from a text box when the value is not "all". When "all" is selected I want the criteria to be "Is Not Null" so it returns everything. What I have is:

    IIf([forms].[mainform].[engineerfilter].[value]="All",([Master].[Engineer]) Is Not Null,[forms].[mainform].[engineerfilter].[value])

    The criteria functions works perfectly when I have anything but "All" in there, but when I have "All" it returns nothing. Any help would be greatly appreciated. Thanks in advanced!

    -ET

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Have you tried building the SQL string in VB, and then executing it?

    Check out Martin Green's site if you have more questions about building SQL in VB (specifically the tutorials called "Access and SQL" Parts 1-6).
    Last edited by nckdryr; 09-06-07 at 16:05.
    Me.Geek = True

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    IIf([forms].[mainform].[engineerfilter].[value]="All",([Master].[Engineer]) Is Not Null,[forms].[mainform].[engineerfilter].[value])

    look like iif(con,con,false)

    IIf([forms].[mainform].[engineerfilter].[value]="All",IIF([Master].[Engineer]) Is Not Null,[forms].[mainform].[engineerfilter].[value],"false false"),"other false")

    think ihave () right
    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. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Just for the heck of it try this:

    IIf([forms].[mainform].[engineerfilter].[value]="All",IsNull([Master].[Engineer])=False,[forms].[mainform].[engineerfilter].[value])

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Sep 2007
    Posts
    2
    Hey All,
    Thanks for your help so far. Here are the yielded results:

    nckdryr: I don't really have any SQL knowledge, and I was trying to find a place to code VBA in queries but didn't see a section for it (i might be wrong)

    Myles: I am trying to do a iif with (con, true, false). I believe (again I might be wrong) that my syntax so far is correct.

    CyberLynx: The coding you gave me yields the same results that I originally had.

    Thanks again.

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Would you mind posting the rest of your SQL statement? We might be able to help you more.
    Me.Geek = True

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I got it to work but it is a little weird. If you are designing your query with the Qeury Design editor in Access then choose the SQL view from the View menu. Can you post your SQL? Otherwise I can try to explain the SQL.

    Add this as a new field:

    IIf(([forms].[mainform].[engineerfilter].[value]="All",([Master].[Engineer] Is Not Null,((([Master].[Engineer])=([forms].[mainform].[engineerfilter].[value]))))

    Then in the criteria put =True.

    This worked for me when I was testing. But it is very ugly.

Posting Permissions

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