Formatting decimal places

[Robbro]

I am importing an excel spreadsheet with calculated numbers into access for
the purposes of preparing a report only. When I import the sheet and try to
prepare my report I am having trouble formatting columns to show only either
2 or 4 decimals instead of the 10 or more its showing now. When I right
click on a # and go to properties, the property sheet comes up, format says
"Currency" with a drop down arrow next to it, but I click on that and a small
empty box pops up beneath it with no choices (I had success on certain
columns selecting "Standard" then setting the desired decimal places). I can
set decimal places (which is directly below format) to anything I want, 1, 2,
3, 4 and so on, but it does not change what shows up on the actual report. I
have tried this in design view and layout view with same results.

 

[Duane Hookom]

Use the Format property of the text boxes in your report design.
I rarely find a need to apply any format in a table or query.

 

[Robbro]

I am in the report design. I cannot get the report to change the decimal
places it shows. Both layout view and design view.

 

[Duane Hookom]

How about Print or Print Preview? Does the field appear right-aligned when
viewed in datasheet view?

 

[Robbro]

Yes the numerous digits show up in print and print preview and yes the
numbers are right aligned.

 

[Duane Hookom]

If they show up in the print and preview then why worry about the other views?

 

[Robbro]

Because I dont want them, I'm trying to get it down to 3 or 4 decimal but
showing 10 or more. Too many digits show up in ALL views and I dont want
them, especially when I print. I cant figure out why I can change the
decimal property settings but it does nothing to how the info is displayed, I
guess its showing it as text somehow for some reason but I cant figure out
how to change that either.
When Clicking Format on the properties tab I get a blank box, I cannot
choose anything.

 

[Duane Hookom]

I expect the fields are being treated as if they were text and not numbers. I
don't know where this might be happening since you suggested the values are
right-aligned when viewed in datasheet view. You may need to convert the
field values to numbers with the Val() function. For instance if you have a
field named [Quantity] then your text box properties might be:
Name: txtQty
Control Source: =Val(Quantity)
Format: Standard
Decimal Places: 3

 

[Robbro]

I finally got around it in a similar way. I deleted the fields in the
reports and added new text boxes and in the control put =round([field],4) and
that works. Trying to change the control on the existing field to
round([field],4) never worked.

I expect the fields are being treated as if they were text and not numbers. I
don't know where this might be happening since you suggested the values are
right-aligned when viewed in datasheet view. You may need to convert the
field values to numbers with the Val() function. For instance if you have a
field named [Quantity] then your text box properties might be:
Name: txtQty
Control Source: =Val(Quantity)
Format: Standard
Decimal Places: 3

--
Duane Hookom
Microsoft Access MVP

Because I dont want them, I'm trying to get it down to 3 or 4 decimal but
showing 10 or more. Too many digits show up in ALL views and I dont want
them, especially when I print. I cant figure out why I can change the
decimal property settings but it does nothing to how the info is displayed, I
guess its showing it as text somehow for some reason but I cant figure out
how to change that either.
When Clicking Format on the properties tab I get a blank box, I cannot
choose anything.