Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter a report for null? (Office XP)

    I want to filter my report to display all records with null values in the "Completed On" column....here's what I've *started* working with...I need a little nudge in the right direction - Thanks!

    Dim stDocName As String
    Dim strCriteria As String
    stDocName = "AwardsMatrix"
    strCriteria = "[Completed On]= 0"

    DoCmd.OpenReport "AwardsMatrix", acPreview, acReadOnly, strCriteria
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter a report for null? (Office XP)

    Null is different from zero (0). Zero is a specific number, while null means that data are missing. Don't think of null as "nothing" but as "it could be anything". Therefore, you cannot check for null values by using

    [Completed On]=0

    as criteria, neither by using

    [Completed On]=Null

    To test for nulls, you must use

    [Completed On] Is Null

    "Is Null" is a fixed expression (to test for non-null values, you would use another fixed expression "Is Not Null"). So try

    strCriteria = "[Completed On] Is Null"

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter a report for null? (Office XP)

    I had tried Is Null....but I'd done it like this: IsNull, messing me up....thanks a lot!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter a report for null? (Office XP)

    I admit it is slightly confusing. In a query, and hence in WhereCondition arguments, you use Is Null:

    <code>[FieldName] Is Null</code>

    but in expressions, for example in the control source of a text box on a form, or in VBA code, you use the function IsNull:

    <code>=IIf(IsNull([FieldName]),"This","That")</code>

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter a report for null? (Office XP)

    Thanks for the info! There is a LOT to learn about these programs....geez
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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