Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    130

    Unanswered: Problem with testing for Null, Blank, Empty

    I have a form field calculated as follows:

    =Sum([CountOfXYZ])

    The [CountOfXYZ] is the result of a count in another query.

    When there is no data found in the search set, it returns a blank or null. This is essentially the correct result, however, I can't seem to test for the blank or null so that I can give a message (or place a value of zero). IsNumeric, IsNull, IsEmpty just don't seem to give a true response.

    Any ideas?

    Winston

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How are you using IsNull?

    IsNull or nz() should do everything you need regarding null evaluation, you could you be more specific about exactly what you'd like to do, and the exact syntax you've tried?

  3. #3
    Join Date
    Apr 2004
    Posts
    130
    A secondary field looks as follows:

    =IIf(IsNull([intCountField]),"Null","NotNull")

    Shows not null when a value is calculated in the [intCountField], but does not show "Null" when a blank (non displaying) appears. The query the calculates the [intCountField] works properly. It just shows a blank when there is no count result. Even if I could get it to show zero then I could test for it and apply a message.

    Is this clearer?

    thanks,

    Winston

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try

    =nz(intCountField, 0)

  5. #5
    Join Date
    Apr 2004
    Posts
    130
    No, that didn't work either. Thanks anyway.
    Any other thoughts?

    Winston

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Winston
    No, that didn't work either. Thanks anyway.
    Any other thoughts?

    Winston
    Wait a sec... nz(yourfield, 0) is NOT returning 0 when the field is null?

    Something is very wrong there...

  7. #7
    Join Date
    Apr 2004
    Posts
    130
    You are correct... something is wrong. Been pulling my hair out on this one. The field that is being tested is a sum of a count field in another query. When the query returns nothing, I get a blank rather than a zero. Which is bearable, but I want to test for the blank and give a message.
    The only thing I can think of is that because the count query ends up with an empty record set the sum function is neither null, blank or zero and that is why all the tests don't work.
    Whatcha think?
    thanks,
    Winston

  8. #8
    Join Date
    Apr 2004
    Posts
    130
    If you want to send me a private message with a phone numbe, I will call you and let you look at my form etc using Glance (internet display of my desktop).
    Thanks,
    Winston

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    cant resist a go at this one:
    iif (len(nz(yourField, ""))<1, "dead", yourfield)

    doubt it will fly!

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Apr 2004
    Posts
    130
    You are correct, it didn't fly.
    It picked up my field, but not the "dead"

    Thanks anyway

    Winston
    Last edited by Winston; 04-16-04 at 15:23.

Posting Permissions

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