Results 1 to 5 of 5
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need "0" in crosstab query (for empty cells)

    Hello Experts:

    I need some assistance with modifying a crosstab query IOT to include "0" values where cells are empty.



    Pls see below two attached pictures illustrating the a) crosstab and b) output of the crosstab.

    Values in field [T102_N1S_Staffmember].[All_MOC_TrainingStatus] can take on two values: "Completed" or "Not Completed".
    These two values are converted as my column headings... now, while the crosstab output itself is correct, I'd like to show a zero, e.g., at intersection "Org 1" & "Completed". The 2nd JPG illustrates it in detail.

    I tried including the NZ function for StaffMemberIDpk but the output doesn't not change.
    Code:
    Expr1: Nz([StaffMemberIDpk],'Not Found')
    Expr1: Nz([StaffMemberIDpk],Null)
    My question: How can I force the output of zero where a value is blank?

    Thank you,
    EEH
    Attached Thumbnails Attached Thumbnails Crosstab.JPG   Output.JPG  

  2. #2
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Never mind... found a solution: ZeroConversion: Val(Round(Nz(Count(Val([T102_N1S_Staffmembers].[StaffMemberIDpk])),0),2))

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you can just use the format property to show nulls as zeros

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax -- coming back to this thread. How do I format the property to "show nulls as zeros"?

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    The format property for numbers has four sections-positive;negative;zero; null so you might have

    #0;[red]%0;0;0

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-09-2019, 05:25 AM
  2. Replies: 3
    Last Post: 06-06-2018, 08:26 PM
  3. Replies: 3
    Last Post: 12-30-2016, 03:55 PM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. Replies: 7
    Last Post: 07-15-2015, 03:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums