Results 1 to 13 of 13
  1. #1
    Join Date
    May 2008
    Posts
    48

    Red face Unanswered: Convert NULL value to 0

    I want to display 0 value in expression in Access Report where it shows NULL value.

    I have tried NZ function as well as IIF function with Is Null crtieria in Query also, but does not show "0" value, as I wish.

    Anybody enlighten me how to overcome this problem.

    Thanks in advance.

  2. #2
    Join Date
    May 2009
    Posts
    104
    I had the same problem in my database, all i did was create an update query that changed all the null values into zeros before i ran the report. This should work for you unless you have a need to have null fields.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is the Nz() function applied on your report or within the query behind the report?

    It should be in the query
    George
    Home | Blog

  4. #4
    Join Date
    May 2009
    Posts
    104
    I didn't use the NZ() function. I had the same trouble with my database, some records contained nulls instead of zeros because i have an cvs file linked to a table in the database and it was confusing my IIF statements. I created an update query that ran as i updated the table from the .cvs file. This is not the same problem you have but if you want to display zeros instead of nulls in your report this should work for you, i think the simple way is to rid yourself of the nulls in your database, i'm almost certain nulls in a database are a no no anyway.
    Last edited by dbshaft; 10-09-09 at 09:29.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by dbshaft
    i'm almost certain nulls in a database are a no no anyway.
    Absolutely false!

    NULLs are more than welcome in a database
    Last edited by gvee; 10-09-09 at 10:16.
    George
    Home | Blog

  6. #6
    Join Date
    May 2009
    Posts
    104
    Quote Originally Posted by gvee
    Absolutely false!

    NULLs are more than welcome in a database
    This wouldn't be the case if you are have problems because of them.
    Nulls in date and text fields are fine, nulls in number fields are unnecessary, but lets stick to the problem at hand.

  7. #7
    Join Date
    May 2009
    Posts
    104
    Do you want to keep the null fields null?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    null have a valid role in the database
    null is not he same as zero
    replacing nulls with zero can introduce subtle errors into some reports and the like, especially when using sumation or statistical functions.

    there are some people who believe that nulls are the sapwn of the devil. for me, used appropraitely NULL is a perfectly valid value. it indicates the absence of value. ie although the rest of the row is valid a null indicates that we don't know the value for that column at present, if ever.

    some purists would have you design a kludgy EAV model to design out the null column as it doesn't sit with their perception of db design and order
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2009
    Posts
    104
    Quote Originally Posted by healdem
    null have a valid role in the database
    null is not he same as zero
    replacing nulls with zero can introduce subtle errors into some reports and the like, especially when using sumation or statistical functions.

    there are some people who believe that nulls are the sapwn of the devil. for me, used appropraitely NULL is a perfectly valid value. it indicates the absence of value. ie although the rest of the row is valid a null indicates that we don't know the value for that column at present, if ever.

    some purists would have you design a kludgy EAV model to design out the null column as it doesn't sit with their perception of db design and order
    I agree, thank you for making me see the light.

  10. #10
    Join Date
    May 2009
    Posts
    104
    In your query were you are running the report off of create a field named the same as the field you want the zero instead of null value to show up in Example. I create a query with a date field called close date. here is the expression that worked

    Close Date: Nz([Close Date],0)

    essentially you replace the field closed date with your expressed field. Sorry about the wording i'm database ignorant.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by dbshaft
    i'm database ignorant
    No, you're just learning
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    No one has mentioned the empty string. It is not clear from the original post what the field type is and an empty string can look like a null when you are viewing the table. This is code that will change empty strings and Nulls to 0:

    IIf([FieldName]&""="",0,[FieldName])

    It works by first appending the empty string to the value in the field and then comparing it to the empty string. Null or the empty string plus the empty string is equal to the empty string.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed with healdem.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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