Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    57

    Unanswered: Query not returning null values

    I think I am just missing something on this one so some help would be great. I am using Access XP and and '03 to run some queries off of an imported SQL table. I have two combo boxes that set my low and high limits for the query. I am isung the IIF, And statement to pull what the combo boxes define as the range.

    Criteria: >=IIF([Forms]![Form1]![cboBoxLowValue] is null, [Fieldxyz],[Forms]![Form1]![cboBoxLowValue]) And <=IIF([Forms]![Form1]![cboBoxHighValue] is null, [Fieldxyz], [Forms]![Form1]![cboBoxHighValue])

    When I run the query with the boxes empty it returns everything but the null values in my table.

    if I simplify the criteria to just pull everything from the field without using the combo boxes IE: [Fieldxyz] - it still only pulls the non null values.

    If I change the query again to pull only null values IE: is null - then it pulls only the null values.

    Anyone run into this at all??

    Thanks for any help

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    When you state a criteria null values are automatically excluded. you need to Add

    or is null

    S-

  3. #3
    Join Date
    Oct 2003
    Posts
    57
    If I change the middle part of each IIF statment to read:

    [Filedxyz] and is null

    then the expression cannot be evaluated because it is too complex.

    Is this what you meant??

    Thanks

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    "or" is null


    Not "and"

    S-

  5. #5
    Join Date
    Oct 2003
    Posts
    57
    Or is null

    still returns an error stating that it is too difficult to be evaluated

    It seems simple enough to me

    Any other ideas?

    Thanks

Posting Permissions

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