Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forcing Zeroes in Crosstab Queries Output (2003)

    If you select August 2005 on the startup form of the attached mdb and then open the QueryFermiM1 query, is there a way to have the query results display zeroes instead of blanks in the empty cells?

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

    Re: Forcing Zeroes in Crosstab Queries Output (2003)

    Change the definition of SommaDiOREF to

    SommaDiOREF: Val(Nz(Sum([OREF]),0))

    and set the Total option for this column to Expression instead of Sum.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Zeroes in Crosstab Queries Output (2003)

    Thank you Hans,
    How come the RFermiPne report still shows blanks in the cells where its record source, which depends on the QueryFermiM1 query, displays zeroes?

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

    Re: Forcing Zeroes in Crosstab Queries Output (2003)

    Because you have explicitly set a number format for the text boxes that hides zero values: the Format property is <code>#,###;-#,###;""</code>
    The first part is for positive values, the second part for negative values, and the third part for zero values. The "" hides the latter. If you clear the number format, or set it to <code>#,##0</code> (not to <code>#,###</code>), zero values will be displayed.
    The number format in the report is not standard, so you must have set it yourself.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Zeroes in Crosstab Queries Output (2003)

    Yes, I did set it myself following your advice <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    In this case we selected August 2005 on the startup form. Is it possible to make it so that the report cells from September onwards remain blank instead of displaying zeroes?

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Zeroes in Crosstab Queries Output (2003)

    How can I go about having the QueryFermiM1com query(please see attached mdb) results display zeroes instead of blanks in the empty cells?

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

    Re: Forcing Zeroes in Crosstab Queries Output (2003)

    This is the same question as the first one in this thread, even if the query is slightly different. The answer is the same too, so please see <post#=505,099>post 505,099</post: >.

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

    Re: Forcing Zeroes in Crosstab Queries Output (2003)

    Francois explicitly mentioned post 418,559 that the "" was to be used if you wanted to hide zero values.

    Put the following code in the On Open event of the report:
    <code>
    Private Sub Report_Open(Cancel As Integer)
    Dim i As Integer
    For i = Forms!MSeleData1!Mese + 1 To 12
    Me.Controls(CStr(i)).Format = ";;;"
    Me.Controls("Sum" & i).Format = ";;;"
    Next i
    End Sub
    </code>

    Added: I forgot to mention that you should rename the text boxes displaying the column sums in the report footer to Sum1, Sum2, ..., Sum12. Sorry about the omission.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing Zeroes in Crosstab Queries Output (2003)

    Thank you so much Hans,
    <hr>Francois explicitly mentioned post 418,559 that the "" was to be used if you wanted to hide zero values.<hr>
    You're absolutely right, I was meaning I set the number format in the report using your(and Francois') advice but I forgot I did. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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