Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Zero if null (Access 2000)

    I have created a datasheet form based on a totals query, and some of the number fields in the results are blank because there were no records found to sum in the query. How can I format these blank fields to appear as zeros?

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

    Re: Zero if null (Access 2000)

    You can use the Nz function in the query to replace blanks with zeros. E.g. if you have a field Amount, with Total option Sum, you can use this:

    SumOfAmount: Nz(Sum([Amount]),0)

    with Total option Expression. If you need the result for further calculations, you may need

    SumOfAmount: Val(Nz(Sum([Amount]),0))

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Zero if null (Access 2000)

    That works fine thanks, Hans.

    I also need to format some numeric fields with commas, so I entered #,##0 in the Format field in their properties, and it worked for all except one field. (For some reason, the Format field for the properties on this form doesn't have the usual list with the General Number, Currency, etc. options.)

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

    Re: Zero if null (Access 2000)

    You can still enter Currency or similar in the Format property, even if it isn't offered in the dropdown list.

    Can you provide information about the field where the Format doesn't work?

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Zero if null (Access 2000)

    I tried entering a format name but it didn't work; however, the form is based on a query that is based on another query, and I discovered that if I used Format([Field],"#,##0") in the first query, it carried through to the second query and the form.

    Also, I can right-align the columns of numbers in a form, but how can I do this in a query?

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

    Re: Zero if null (Access 2000)

    You cannot set the alignment of values in a query, but that doesn't matter, since the end user should never work with a query directly, only with forms and reports.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Zero if null (Access 2000)

    OK, understood thanks. I am having some difficulty creating a form from a query 502,250 and need to use the query directly for now.

Posting Permissions

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