including non-significant digits (XP)

[Bob Sullivan]

Hello,

I would like to show two decimal places, regardless of
significant digits, in a worksheet (1.00). I know I can
format the numbers to show two digits in the sheet, but
when checking the number in the formula bar, it still
does not show the non-significant digits.

The problem of not holding non-siginificant digits occurs
when linking the worksheet as a table in Microsoft
Access. I need the non-significant digits to display in
Access. You can't change the format of a linked table in
Access.

What compounds the problem is that in Office 97 I could
do this and the digits showed up. Now that the software
has been upgraded to XP, they don't.

Any suggestions?

Thanks,

Bob Sullivan
Springhouse

 

[Jim]

The value in the formula bar is the actual value, and AFAIK does not display
extraneous data/formatting. You may format the cell(s) to display the value
differently, but you cannot do so in the formula bar, your memories of XL97
notwithstanding.

 

[George Nicholson]

Bob:

You can't change the format of a linked table in Access.

Double check that statement. There are certain field properties that you
can't change for linked tables. You can't change the data type of the field
(Text, Number, etc.) but you can change the FORMAT property of that field
(i.e., how it is displayed) and the Decimal places, as long as the Data
Type has been defined as a Number or Currency field by the wizard that did
the linking. Or, at least I can in Access XP.

If the problem is that Access has defined a Number field as having a
FieldSize property of Integer or Long Integer (thereby preventing the
storage, but not display, of decimals) then maybe you can force the issue by
making sure that some of the first records in the table you want to link
actually contain decimal values other than .00. AFAIK, the wizard only
looks at the first few records when determining an appropriate data type for
what it assumes is "typical" data. This sometimes causes problems like this.
Once the link is established (and the "proper" data type has been
established within Access) you should be able to change any dummy decimal
values back to .00 in the source table if you need to.

Actually, I'm not sure even sure that should be necessary to do that. I
just linked an Excel test file to Access and as long as the entire column
was formatted as having decimals, the field was defined as having a DataType
of Double (or Currency), even when there was no data to the right of the
decimals. Have you applied all the Service Packs for Office XP? I don't
know if this was one of the issues addressed by the SPs, but I also don't
know if this is actually the problem you are having.

Last thought: when linking to an Excel file in Access you are given the
choice to select a worksheet or a named range. Sometimes using the named
range option can help in working around problems like this if there are
extraneous header/footer rows that may be confusing the linking wizard.

Good Luck,
Hope this Helps,