Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Defualt to zero (2002)

    Hi!

    I have a report that is populated from a form in which the user enters reasons a potential employee wasn't hired. The user chooses the reason from a combo box and the then enters the corresponding total. This all works fine. The problem is that the reasons may vary each time the report is run, resulting in months were there would be no value for a reason. For example in Jan and March, "Bad background checK" had data but in Feb and April there was no data. When the report is viewed the user can't tell if the data is missing or there is no data for these choices.

    I had originally set the default value in the table to zero but that doesn't do the trick. I'm wondering if there is something I can put in the print event of the report or somewhere else.

    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Defualt to zero (2002)

    Does your report work from a cross-tab query? If so, you may want to specify the column headers for the query so it always returns data.
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Defualt to zero (2002)

    Hi Wendell,

    Yes it does run from a crosstab query and the column headers are set. Here is the SQL:

    TRANSFORM Sum(qryPIEmployeeOccurance1.TotalInjuryByType) AS SumOfTotalInjuryByType
    SELECT qryPIEmployeeOccurance1.Year, qryPIEmployeeOccurance1.OfficeName
    FROM qryPIEmployeeOccurance1
    WHERE (((qryPIEmployeeOccurance1.Year)=[forms]![frmPIReports]![txtYear]))
    GROUP BY qryPIEmployeeOccurance1.Year, qryPIEmployeeOccurance1.OfficeName
    PIVOT qryPIEmployeeOccurance1.Month In ("1","2","3","4","5","6","7","8","9","10","11","12 ");

    I've attached a pic of how it looks. This is a subreport, therefore the actual column headings don't show on this view.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Defualt to zero (2002)

    You can use the NZ function in the report to replace Nulls with zeros.

    I attach a demo.
    Regards
    John



  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Defualt to zero (2002)

    Thanks John I will give this a shot.

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Defualt to zero (2002)

    Hi John,

    Sorry it took me so long to reply back but I didn't have internet access since Saturday!

    I tried your example but the result returns #error. I'm trying to post a stripped down version to give you an idea of what I'm dealing with but the report is huge.

    Leesha

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Defualt to zero (2002)

    Hi again,

    Attached is a stripped down version of the report. The master report (not included here) includes many subreports with subreports in each of them. In the sample that I've attached, the main report to open is titled rptHelpingHandsStudy. The data in the report is correct. There are two "visual" problems. In the first portion of the report, title "Total Helping Hands Referrals", all blanks should = zero. In the second two % portions of the report, blanks need to = NA. this is because since there were no referrals for the month, there was nothing to score them on. 0% for compliance in this area would be the wrong result.

    You will see in queryHelpingHands1, that I've set the filter to only show records where [no admits]=no. I realize this keeps the zeros from showing in the first section of the report. I did this because it resulted in the % sections as reading 0% and I couldn't find a way to make them state "NA".

    Thanks,
    Leesha

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

    Re: Defualt to zero (2002)

    See the attached version. I have used custom formats for the text boxes in the subreports: <code>0;;;0</code> for the first subreport, <code>0%;;;"NA"</code> for the other two.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Defualt to zero (2002)

    The solution from Hans with custom formats is a very simple solution.

    When you use the NZ function in a report, you can't use the same name for the control as its control source.
    If you put a textbox on a report with control source 1, then Access calls the text box 1. If you then put in the NZ function = NZ([1],0) get the error message until you rename the text box txt1. (Unless you have name autocorrect turned off, Access then goes and puts txt1 in other places you don't want.)

    The NZ function returns a string, so you numbers become left aligned instead of right aligned also. My example put a Val function around the NZ function to turn it back to a numer.
    Regards
    John



  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Defualt to zero (2002)

    Hi Hans and John,

    The report is gorgeous and looks exactly as I need it to look. I've read both explanations as to what to look for to understand where the formatting changes took place so I can understand it and duplicate it in the other subreports that have similar issues. As usual my reaction is WOW! Again, a totally new learning experience and another world of possibilities.

    Question: are there always 3 semi colons inbetween and what do I look up to find more info on this way of formatting.

    Thanks! It's beautiful!

    Leesha

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

    Re: Defualt to zero (2002)

    Click in the Format property of one of the text boxes.
    Press F1 to get online help on Format.
    Click the link for numeric and currency data types.
    Scroll down until you get to custom formats.
    The help file explains that the Format property may contain up to 4 sections, separated by semicolons, what they mean and what happens if you omit one or more sections.

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Defualt to zero (2002)

    Thanks Hans! I'm amazed at the possibilities.

    Now one more question. I woke up in the middle of the night with the thought that although my boss will be thrilled (and will get off my back) that there are now zero's and NA's in the report her next thing will be is there a way not to have all the NA's in the months that we haven't got data on yet. (I need to find her something to keep her busy!) For example, the example I sent has data through April. The fix to it is perfect for my needs, but she will not want all the NA's for May, June, July etc as those months haven't come yet. (Each of the columns in the report represents a month and the main report has the headings).

    I went back and tried Johns suggestion of using an unbound text box with a different labels such as txt1, txt2 and the code =(nz([1]),0) and that works fine for areas where there are no percents being shown, and it only populates months where data has been entered. My problem is that for percents, it enters a zero percent which would the wrong answer. I tried =(nz([1]),"NA") but got nothing in return and then tried =iif(nz([1]),"NA") but also got a blank return. I'm wondering if there is a way to get the report to show the data the way you've formatted it but to only show it up to the month that there has been data reported for. If not, my boss will have to live with it the way it is. Personally I'm thrilled!

    Thanks,
    Leesha

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

    Re: Defualt to zero (2002)

    The Nz function takes two arguments, so the parentheses are in the wrong place. Try

    =Nz([1],"NA")

    instead of

    =(nz([1]),"NA")

    BTW Access doesn't know whether a null value is for a month that has data but not for this particular row, or for a month that has no data at all.

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Defualt to zero (2002)

    Thanks Hans! That worked and the explanations helped a ton. Now I've two options to present my boss!

    Have a great day,
    Leesha

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Defualt to zero (2002)

    I'm sure you already knew this would happen (as I now understand you were trying to point out), but the result using the text boxes was the same as changing the format as you had me do. As an update, what I decided to do is to put dashes in where there are null areas. This way the user knows that the information wasn't omitted and the report doesn't get overly busy in th months that haven't been populated yet. I used 0;;;- and that worked. My boss is OK so its a win. Best part though, I learned a great deal with all of your help!

    Thanks,
    Leesha

Posting Permissions

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