Formatting problem Access 2013 report


Simple problem: I have a report (based straightforwardly on a table) where the value in one column is mostly 0, sometime set, so I'd like to hide it when it's 0.

To slightly complicate matters, every second line in the report has a light grey backgroud ("zebra effect", which is handy to make things more readable in a landscape format), so I can't use the white font trick, because you see the value on grey background lines.

Also, the field should always be shown with 4 decimals, so even if I define a format such as "#", it shows all the zero values because of the 4 decimals. If I set the decimals to Auto, it works, but shows me the value without decimals... Catch 22, kind of

I've tried to put a call to a small function (which would return "" or a properly formatted value for the field of that column), but that failed miserably with a "#Type!" error. The content of the report field was "=myFunction([table field name]), the function was defined as Public in one the module of the app, taking one argument (of the type of the field name) and returning a string, but Access didn't like that. No idea why.

Anybody can tell me how to do it ? It's seems such a simple problem, and yet I can't get it to work.

bthouinIT Analyst and developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bthouinIT Analyst and developerAuthor Commented:

I've forgotten to mention that of course I tried first the "fancy" formats with 4 parts, but every time I had finished to enter the format and moved the cursor, all the "#" characters in the format disappeared, so basically, it seems the format syntax in a report field is not behaving like in a query, so at the end I gave up on formats and tried the other things described in the first post.

And I just tried now to first define a query on the table, and in that query a call to my function works and gives me the text I want, so that might be the solution. But as I have lots of columns from the table in the report, I'd like to avoid defining a query in between the table and the report. But maybe that's the only proper solution ?
Gustav BrockCIOCommented:
You can use this format string:


As a property, it may read:


but I haven't tested that.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bthouinIT Analyst and developerAuthor Commented:
The property reads :


after inserting


and it works fine !

Thanks  Gustav

Have a nice weekend :-)
Gustav BrockCIOCommented:
Great. You are welcome!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.