Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    0 instead of blank (2000)

    Is there a way in a report to put 0's where there are blanks? Is there some property? Maybe vba on the "on Format" of the detail section?

  2. #2
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 0 instead of blank (2000)

    It depends on exactly what you've got & what you want.

    If by blank you mean null & the field is numeric the function Nz([YourField]) will replace null with zero as will IIf([YourField]=null,0,[Yourfield]).
    If by blank you mean "" ie an empty string then IIf([YourField]="",0,[Yourfield]).

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

    Re: 0 instead of blank (2000)

    You can specify a custom Format with up to four sections, separated by semicolons:
    - First section for positive values
    - Second for negative values
    - Third for zero values
    - Fourth for null values
    For integers, you could use 0;0;0;0

    You can also use the Nz function: set the Control Source of a text box to
    =Nz([FieldName],0)
    Note: make sure that the text box doesn't have the same name as the field, this leads to a circular reference.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 0 instead of blank (2000)

    Where do I put the custom format? In the table?

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

    Re: 0 instead of blank (2000)

    If you put the custom format in the properties of the field in the table, this format will be copied when you put the field on a form or on a report. But the format of already existing text boxes bound to the field will not be modified. So it might be easier to set the Format property of the text box on the report.

Posting Permissions

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