Formatting of a field does not get carried over to the form

[MNNovice]

This is rather a simple problem nevertheless I have been struggling with this. I am sure one of you experts will show me the way to correct this in a flash. Please help.

Two of the many fields in tblGrantFunds are:

FundID Number (Field Size: Long Integer)
FedRatio Number (Field Size: double, Format: Percent)

A query based on this table is qryGrantFunds which shows the Fed Ratio as nn% (80% for example).

I have a form Sub form sfrmAP that's based on tblAP where I added two text boxes
1) txtFund has this in its control: GrantFundID, Row Source: qryGrantFunds

2) txtFedRatio has this in its control: =cboFund.Column(4)

Formatting of FedRatio shows up as .8 in the form and not as 80%. What am I doing wrong?

Thanks.

[Stewart Ross]

You're not doing anything wrong at all. Access does not actually do much in the way of carrying forward formatting; the form wizard does do so, but if you add textboxes yourself you will need to set the format property for that textbox appropriately yourself.

A word of warning about your combo column reference: you may find that the combo is returning a text value for your FedRatio value, in which case no amount of formatting will do any good (as formats are applied to numeric values, not text ones). Simple answer in that case is to replace the =cboFund.Column(4) control source with

=CDbl(Nz(cboFund.Column(4), 0))

which will ensure that a floating-point numeric value is being returned. You can then apply a custom format in the Format property of the textbox to display it as a percentage.

The Nz is just ensuring that in the event of a null value in the combo column a text 0 is returned instead. CDbl would otherwise fail on the null value, returning #Error instead.

-Stewart

[MNNovice]

Stewart:

Wow! It fixed the problem. Thank you thank you thank you so much. I learned a new thing here about Access i.e., floating-point numeric value. What does CDbl do?

Many thanks.

[Stewart Ross]

Glad to be of help!

CDbl is one of the type conversion functions - in this case convert to double-precision type. I suggested CDbl instead of its counterpart CSng (convert to single-precision) because of the greater number of significant digits which the double-precision type uses (15 instead of 7), as rounding errors can be troublesome in low-precision types. I could have suggested CCur - convert to Currency type - as it does not suffer from rounding errors at all. However, I didn't want to make it more difficult for you in subsequently formatting the value - currencies would by default include the local currency symbol for your area and be formatted to two decimal places, although this can be changed of course.

What is perhaps poorly understood in the use of combo or listbox columns is that the combo is a generic container for values - it is untyped, unlike the underlying field you bind the combo or listbox to in your table.

As text types can be used to represent all kinds of values without having to know in advance what the type really is, combo columns are actually returned as text. They may look like numbers, but they don't behave as numbers. Access will convert these 'on the fly' if, for instance, you feed a column value into a calculation. In your case you were trying to display the value returned by a combo column and it just displayed as it really was - as text. CDbl in this case just converts the text value to a true number ready for formatting or for use in further calculations.

Cheers

Stewart

[MNNovice]

Stewart: That was really a good elaborate explanation. Thank you.