formatting?

[hi there]

hi everyone,

i have 3 quick questions. they all relate to unbound calculated controls (text boxes) on a form. i have a form with a section that looks similar to a spreadsheet. there are 3 columns (controls) and the first two are bound text boxes. the third column is a calculated control (text box) which returns the product of the first two columns and scales the value. my questions are as follows:

1. the calculated control is returning values with 6 decimal places and i want to limit it to two. i went to the text boxes' properties and under "format" i selected 2 for decimal places, but it still is returning 6 decimal places.

2. on that same calculated control i want the default value to be zero because the corresponding bound controls (text boxes) that the calculated control is references can be Null. so if this happens i want the calculated control to return zero. i went to the calculated controls properties and under "data" i set the default value to "=0" but the calculated control is empty.

3. lastly i have a calculate control that sums the values of the all the calculated controlsl previously mentioned. i input the formula but the text box doesn't return anything. i check my references with the code build expression builder and it seems to be correct.


if anyone could help me with these problems, i would be extremely grateful. thanks everyone and have a nice day.

 

[Vassago]

Try this:

For the control source, type this:

=IIf([number1] Is Null Or [number2] Is Null,"0",Format([number1]/[number2],"0.##"))

Basically, it says, if either number 1 or number 2 is null, then number 3 = 0. Otherwise, it will number 1 divided by number 2 with a limit of two decimal places.

You can play with the formula in the format parenthesis until you get it the way you need it. Also you can change the formula in quotes to display two decimal places every time by changing the #'s to 0's as follows.

=IIf([number1] Is Null Or [number2] Is Null,"0",Format([number1]/[number2],"0.00"))

Hope that helps.

 

[jjturner]

Some added perspective

I wanted to offer some thoughts on this as your situation exposes some interesting and not so obvious calculation behavior.

Firstly, aside from creating a 'user-defined' format, you can assign one of the built-in formats in the Format property of your calculated control to set the decimal place at 2 - use either 'Fixed', 'Standard', 'Currency', or 'Percent'. (If you try to set the Decimal Places in the properties without assigning one of these formats, Access will disregard your decimal place definition.)

Vassago and Pat rightly and efficiently address your concern with Null values by using the IIf solution.

However, in further explanation of why you're unable to normally see the '0' as a default value is because 'Null' has already propagated through your expression and evaluated your calculated control to 'Null' before it has a chance to default to '0' (because it's already looking at the 'Null' values in your two number fields).

A slightly more bizarre solution for this would be to set the ControlSource in your calculated control to the following:

=Nz([Number1]/[Number2],0)

... or even weirder:

=Nz([Number1]/[Number2])+0

You may wish to lookup the 'Nz' function and its attributes in Access help for future reference.

As for your summation field, 'Null' value(s) are likely 'eating-up' all the other legit values in your calculated controls, so once you successfully handle Null values at the record level, you should be getting good results at the summary level.

Regards,
John

 

[hi there]

i just realized i forgot to thank everyone for their wonderful responses to my question. thanks again all.