Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with null criteria in an IIF statement (2000)

    I have an unbound form, that I'm using for query criteria - it has a field called [txtClearedBy].

    If I have a value in [txtClearedBy], the following criteria works OK:

    IIf(IsNull([Forms]![frmQueryCriteria]![txtClearedBy]),Like "*" & [Forms]![frmQueryCriteria]![txtClearedBy] & "*",[Forms]![frmQueryCriteria]![txtClearedBy])

    If there is no criteria supplied in [txtClearedBy], the query instantly returns an empty recordset, rather than processing the Like "*" & [Forms]![frmQueryCriteria]![txtClearedBy] & "*".

    Basically, the problem is that this statement works if used outside of my IIF, but not from within it.

    I would pull some of my hair out, but I'm already bald. I'm sure that the answer is staring at me, but I've been looking at this problem for so long that I can't see it.

    If anyone can point out a solution, I'd appreciate it.

    Thanks,

    Howard

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problem with null criteria in an IIF statement (2000)

    Try:
    [Forms]![frmQueryCriteria]![txtClearedBy] is Null or Like "*" & [Forms]![frmQueryCriteria]![txtClearedBy] & "*"

  3. #3
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with null criteria in an IIF statement (2000)

    You know, Pat, that works, but I wouldn't have guessed that for a while. I guess that the [Forms]![frmQueryCriteria]![txtClearedBy] Is Null part returns sort of a 'True' condition? Hope I remember this one next time. Thanks again - Howard

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problem with null criteria in an IIF statement (2000)

    I think you could also try:
    Like "*" & ([Forms]![frmQueryCriteria]![txtClearedBy] + "*")
    What the + does is it evaluates the expression between the round brackets and if one of the fields ([Forms]![frmQueryCriteria]![txtClearedBy]) is Null it will evaluate the expression to Null.

  5. #5
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with null criteria in an IIF statement (2000)

    Well, the new problem with this is that surrounding the criteria with asterisks like *criteria* returns everything with the criteria within the field; for instance *2* returns 2, 22, 23 26, etc.

    Howard

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Problem with null criteria in an IIF statement (2000)

    But that is exactly what you showed in your original post. What do you actually want it to be? Do you want all records if the criteria is null?
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problem with null criteria in an IIF statement (2000)

    Wildcards have the following effect:
    *2 this returns all records that start with 2, eg. 2,22,23,26
    *2* returns all records where 2 appears anywhere in the field, eg. 32,23,432
    2* returns returns all records ending with 2, eg. 2,32,432

    As Charlotte has eluded to, what do you want to do?

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Problem with null criteria in an IIF statement (2000)

    Pat,
    Shouldn't this
    <hr>*2 this returns all records that start with 2, eg. 2,22,23,26
    *2* returns all records where 2 appears anywhere in the field, eg. 32,23,432
    2* returns returns all records ending with 2, eg. 2,32,432<hr>
    be this:
    2* this returns all records that start with 2, eg. 2,22,23,26
    *2* returns all records where 2 appears anywhere in the field, eg. 32,23,432
    *2 returns returns all records ending with 2, eg. 2,32,432
    Wendell

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problem with null criteria in an IIF statement (2000)

    Wendell you are quite right, but not if you are dyslexic. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Fingers and the brain ?????

Posting Permissions

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