I used a text box in a form to create a calculated value based on two fields in the form: sales and cost to yield profit. I would like profit to appear with two decimal places all the time -> for example 5.55 or 6.00. So that you know, I have set Format: to General Number and Decimal places to 2. The result however is either 6 with no decimal places or 5.230498230498203.
You could try using the CCur() function in your calculations:
In a Query: Profit: CCur([Sales] - [Costs]) in the Field Row of a new column.
In a Form/Report: =CCur([Sales] - [Costs]) in the Control Source of a Textbox.
Failing that, use the Format([Sales] - [Costs],"#,##0.00") function.
Thank you for you help. The first option did not work, but the second did the trick! In trying to understand the formatting of the Format() function, I did some research and found a link which might be helpful to some.
Hi, one more quick question. The calculated fields show "#Div/0!" before values are entered into the revenue and cost lines. I have tried entering "null" into the default value under properties, but no luck. Is there a way to have the calculated field look more appealing / less confusing prior to data entry?