# How to hide zero number on Access 2010 query builder

• ### 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

• 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)))```

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)))```

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`

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

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 ! :)