Results 1 to 8 of 8
  1. #1
    ebroome
    Guest

    Nz Function (Access 2000)

    Where is the Nz function placed in database and how is it placed? I need to convert blanks to zeroes so calculations can be done (Just simple addition of 81 different quantities)

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

    Re: Nz Function (Access 2000)

    You use Nz in expressions, including in calculated fields in queries, forms and reports. However, the only thing it converts is null values. It doesn't do anything with empty strings, etc. In a query, you might create an expression like this: Nz([FieldName],0) to return a zero if there was no value in the field "FieldName".
    Charlotte

  3. #3
    ebroome
    Guest

    Re: Nz Function (Access 2000)

    Thanks for the info but I'm a lttle confused. You mentioned it doesn't do any thing for empty strings. Is a blank considered a null? I understand the Nz expression but where do you put it in a query, form or report? Properties? or where?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nz Function (Access 2000)

    The Nz function normally is used in queries (to prevent null values coming up in the results) and in reports (same reason).
    Maybe you can consider using the iif() function to check for empty strings: iif(strYourField = "","empty string", "non empty string")

    Bart

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

    Re: Nz Function (Access 2000)

    The point is that you can't tell a "blank" by looking at it, because a field with an empty string in it or with spaces in it *looks* blank but isn't, and a field can be blank without being null. Where you put the Nz function depends on how you want to use it. There is no hard and fast rule or the help files would provide enough information for you. Where do you want to do this, in a query or in a report?
    Charlotte

  6. #6
    ebroome
    Guest

    Re: Nz Function (Access 2000)

    I guess I didn't make myself too clear. I created an access database to hold the church weekly offerings and special offerings of the parishioners. I then imported the data we had in a data file in the old Q&A. When we were using Q&A data file, when no offering was made on a particular Sunday, there were what looked like blanks, at least they printed as blanks at the end of the year. I assumed these blanks were imported into the new Access table. When I designed the table I set the default as "0". but the only time the "blanks" are set to zero is when I add a new person. How can I set the "blank spaces" to zero?

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

    Re: Nz Function (Access 2000)

    Default values only apply to new records. If you import nulls/blanks into a record, the default doesn't apply. You'll have to use an update query to replace the nulls will 0 in that field.
    Charlotte

  8. #8
    Lounger
    Join Date
    Aug 2001
    Location
    Texas, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nz Function (Access 2000)

    Why not use an update query to set all nulls and zero length strings to 0?

Posting Permissions

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