none
How to hide zero number on Access 2010 query builder RRS feed

  • Question

  • I am trying to hide zero numbers (show as empty space) so the data be easier to read. I am using Access 2010 query. Any idea how to do this? Thanks


    JayZ

    Thursday, May 15, 2014 5:18 PM

Answers

  • Similar to the IIF you have already constructed.

    58: IIF(Sum(IIF(Nz([CaseAge]) = 58, 1, 0)) = 0, "",  Sum(IIF(Nz([CaseAge])=58, 1, 0)))
    
    58: IIF(Sum(IIF(Nz([CaseAge]) = 57, 1, 0)) = 0, "",  Sum(IIF(Nz([CaseAge])=57, 1, 0)))


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Monday, May 19, 2014 1:00 PM

All replies

  • Similar to the IIF you have already constructed.

    58: IIF(Sum(IIF(Nz([CaseAge]) = 58, 1, 0)) = 0, "",  Sum(IIF(Nz([CaseAge])=58, 1, 0)))
    
    58: IIF(Sum(IIF(Nz([CaseAge]) = 57, 1, 0)) = 0, "",  Sum(IIF(Nz([CaseAge])=57, 1, 0)))


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Monday, May 19, 2014 1:00 PM
  • Thank you so much!! It work great, you are the best!!!

    One more question if you do not mind! What am I missing for below code.

    Thanks


    JayZ

    Monday, May 19, 2014 8:06 PM
  • Between is not a valid operator in an Immediate If, try

    Nz([CaseAge])>= 58 And Nz([CaseAge]) <= 60


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Tuesday, May 20, 2014 9:03 AM
  • Hi Paul,

    Your suggestion is working great.

    58-60FTE: IIf(Sum(IIf(Nz([CaseAge])>=58 And Nz([CaseAge])<=60,1,0))=0,"",Sum(IIf(Nz([CaseAge])>=58 And Nz([CaseAge])<=60,1,0)))

    Question: when I add two other Criteria to the formula is showing (#Error) instead of empty apace. 

    STD and AvailableHours are "Numbers" (example: STD is 6 and  AvailableHours is 7.5). I would like to calculate [58-60FTE] by dividing [STD] and also Divide it by [AvailableHours] 

    58-60FTE: IIf(Sum(IIf(Nz([CaseAge])>=58 And Nz([CaseAge])<=60,1,0))=0,"",Sum(IIf(Nz([CaseAge])>=58 And Nz([CaseAge])<=60,1,0)))/[STD]/[AvailableHours]

    What do you suggest to do to hid zero (show as empty space) so the data be easier to read?

    Thank you very much!!!!!!


    JayZ

    Thursday, May 22, 2014 6:56 PM
  • If your Query column is getting very complicated, as it is getting to a stage right now. I would suggest two things (1) move this into a function where you can better handle data, or (2) break the calculations column by column and then populate the final result.

    If I were you, I would go through a Function route. The only reason being you have to write an IIF outside the current complicate If, then if it not 0 divide, else empty space. So as you can see it will be three more lines. If you are breaking calculations, you might hit the block of the column count or "expression too complex to calculate errors."

    If you are unsure of how to write functions, explain what the logical flow (involving all cases) should do, I will help you write it ! Good luck ! :)


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Friday, May 23, 2014 10:52 AM