zero supersion within a database

[Glenda]

I created a financial database for a client and I had to use a default of
zero for all of my currency type of fields in my payments table in order for
my calculations within my querries to work. Now my client has told me that
the zeros are "distracting" and can I supress them in the report. Is there
any way to do conditional formating in access like you can do in Excel.

 

[Graham R Seach]

Glenda,

That's not surprising, but be warned, the next request will be to supress
invoices/statements that have a zero balance.

The easiest way to suppress zeros is to get rid of the default value in all
the relevent fields, and then update the database tables to get rid of all
zero values. Then all you have to do is ensure all calculations account for
Null values.

To get rid of all default values in tables, open each table one-at-a-time,
and clear the DefaultValue property. Then save the table.

To get rid of existing zero values in tables, create an UPDATE query to
change all occurrences of zero to Null. Do this for every table.

Go through every place you have calculations using fields from these tables,
and wrap the field names in the Nz function. The Nz function converts Null
values to whatever you specify (in this case, zero). We do this, not to
display zero, but to prevent errors by trying to calculate with a Null
value. For example, if the field you're using is called Balance, then you
wrap this field name like so:
Nz(Balance, 0)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Jamie Collins]

The easiest way to suppress zeros is to get rid of the default value in all
the relevent fields, and then update the database tables to get rid of all
zero values. Then all you have to do is ensure all calculations account for
Null values.

Change the data model and business logic for the convenience of
suppressing zeros in the reports? Sounds a bit drastic to me! Anyhow,
currency values defaulting to zero makes a lot of sense but it's hard
to pass judgement without knowledge of the business rules.

I don't know MS Access reports but surely this is basic functionality
for a report writer? For example, in Crystal reports I can write a
formula to conditionally hide/suppress fields, sections, groups, etc.

Jamie.

--

 

[Graham R Seach]

Jamie,

<<Change the data model and business logic for the convenience of
suppressing zeros in the reports?>>
You did when you set the DefaultValue=0 because your calculations didn't
work! But no, not just for that reason.

In financial systems, a zero value has meaning just as other numeric values
do. Null values also have meaning. So to design your schema and business
logic to offer the flexibility of using Null values, zero values and other
numeric values, is to provide the ability to determine the meaning of
numbers (or lack thereof). It is of little consequence to implement an Nz()
function for cases where a number (zero or otherwise) is necessary.

For example, if you always write $0.00, then how do you determine which
records have entered $0.00, and which records have had no dollar value
entered? There IS a difference! Worst case scenario: an Auditor could argue
that a design that enters $0.00 by default, stores erroneous values.

<<I don't know MS Access reports but surely this is basic functionality for
a report writer? For example, in Crystal reports I can write a formula to
conditionally hide/suppress fields, sections, groups, etc.>>
You can do the same thing in Access reports. You have two basic options:
1. Modify the query on which the report is based, so it filters out any
invoice line items that are $0.00.
....or...
2. Open the report in design view. Select the control in question, then
select [Conditional Formatting] from the [Format] menu. Create a conditional
format that specifies white for ForeColor and BackColor if the [Field Value
Is] [equal to] 0. Click [Add >>], then [OK].

But I'd still recommend modifying your schema and program logic.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[John Vinson]

I created a financial database for a client and I had to use a default of
zero for all of my currency type of fields in my payments table in order for
my calculations within my querries to work. Now my client has told me that
the zeros are "distracting" and can I supress them in the report. Is there
any way to do conditional formating in access like you can do in Excel.

You can indeed (in A2000 and later versions); select the Format menu
option and choose Conditional Formatting.

Or, you can set the Format property of a textbox to a "four part
format" - e.g.

$#,##0.00;($#,##0.00)[Red];"";"Unspecified"

will display $3,234.05 for positive, ($328.50) (in red) for negative,
blank for zero, and the word "Unspecified" if the field is null.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Jamie Collins]

I'd still recommend modifying your schema and program logic.

1. You've mistaken me for the OP.
2. Obviously you have no qualms about passing judgement on the schema
design without knowledge of the business rules!

Jamie.

--

 

[Graham R Seach]

Jamie,

Yes, sorry for confusing you with the OP.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Danny]

Hi Graham

Your reply on this impressive , would you be able to help on the following
================================================
My table has
Grouping for
Current Assets - broken down into its parts e.g. cash,
stock, prepayments
Current liabilities - Creditors, payable , accruals
Long term debt
Share capital - pref . ord
retained earings

all the data is in 2 column current and comparatuves
amounts

But need to sub total at the right place in the above
structure.

Thanks
Danny

-----Original Message-----
Yes I have. What do you want? Without seeing your table
structure it is hard to give any advice.

Jim/Cjris

====================================================