Hiding Zeros

[GlenMacK]

I have a report that shows a time sheet for a weekly period. I have each field set to a default = 0.. I need this "0" to show in the form used for entry, but not on the report.

Is there anyway I can have the fields not show on the report if they equal "0". There is about 50 fields on the report that could equal "0".

Thanks IN Advance..

GlenMacK

 

[ZmrAbdulla]

something like this with IIF?
[tt]
=IIF([Controlsource]>0,[Controlsource],"")[/tt]

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[GlenMacK]

I tried that.but I get an error..

 

[ZmrAbdulla]

What type of error?

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[GlenMacK]

it just says "#error" instead of the value..almost like I called the cotnrol Source?? But I double checked, I have the correct Control Source

 

[ZmrAbdulla]

According to the help file it should work, but not when I tried too.
Better way to do it in a query. That works..
[tt]Expr1: IIf([ControlSource]>0,[ControlSource],"")[/tt]

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[GlenMacK]

This may sound stupid..but where would I put the query.I already have a query that gives me the results for the report.

I have over 70 fields on the report..would I need a query for each field

Thanks so much for your help so far.

GlenMacK

 

[ZmrAbdulla]

You don't have to create another query. You need to alter the fields in the query.
Suppose you have a field called "Rate" in the query, you need to alter the field to
[tt]
Rate1: IIf([Rate]>0,[Rate],"")[/tt]

Remove the table name from the grid. and change the controlsource of the text box on the report from "Rate" to "Rate1"

Hope this helps

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[GlenMacK]

perfect..thanks for the help.
Have a great day

 

[dhookom]

There is no reason to mess around with the control source or query when you can set the format property to not show 0s.

Format: #,###.00;-#,###.00;"";""

You can select several controls at once and set all of their Format properties at once.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dcurtis]

You could also set the conditional formatting (I don't remember which version of Access started supporting this). If the control is equal to zero, fore color is white (or whatever color paper you are printing on). You can also select several controls/fields and change them all at once.

 

[ZmrAbdulla]

That are two good suggestions..



________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal