Table design: Setting 3 decimals rounds down to the first decimal, then all 0s

[D'Anconia]

Hi from a 1st-timer! Kind of surprised you don't have a subforum specifically for table design, so I hope this topic is OK for this one.



OK, what's up with this! I'm setting up a table that has several percentage fields, for which I want to display 3 decimal places in datasheet view (and later in forms and reports). I've tried to set up the fields in a variety of ways, but either I don't get a percent format, or I get 3 decimals with 2 zeros at the end. For example, 73.913% shows up as 73.900%. This, of course, causes problems in calculations, so that for a total percent field I'm getting things like 99.900% instead of 100.000%. I've tried various combinations of formatting properties for these fields (Field Size, Format, Decimal Places, and Scale) but I have yet to hit on the right one to simply display the correct value in the format of a percent. A web search has proven daunting.

Can anyone please tell me the specific, proper combination of properties that will fulfill my needs?

Thanks very much in advance!

[June7]

Can't replicate issue. If you want to provide db for analysis, follow instructions at bottom of my post.

[orange]

How about showing the readers some of the code/definitions you used?

You may wish to review the Access Round() function here

[D'Anconia]

Dear June7 and orange,

Thanks for your responses. It took me a little time to get back to you. Attached is a 1-table database with 200 records from the table in question. For an example of the problem, go to the row for SelectionId #18. Column 04 is arrived at by dividing Column 03 by Column 09, in this case, 17 / 23. The result should be 73.913%. Similarly, the figure in Column 07 (which is Column 06 divided by 09, or 6 / 23) should be 26.087%, not 26.000%, so that when it is combined in Column 10 with Column 04 for a total %, it should yield 100.000%. But, of course, it can't be that easy.

I currently have all the "pc" (percentage) fields set up as:
Field Size: Decimal
Format: Percent
Precision: 7
Scale: 7
Decimal Places: 3

By all the definitions I can find on these properties, this is what makes sense. But it doesn't work as expected. I admit it does look suspiciously like a rounding problem, but I did not call any kind of round() function anywhere in the creation of these fields (that I know of). It's all just straight division.

If you guys can figure out what's going on and tell me how to fix it, I'd be very grateful.

Thanks in advance for your efforts!

[orange]

??? Not sure

I did set up this test in vba

Sub testDec()
    Dim x(3) As Double
    x(0) = 17
    x(1) = 23
    x(2) = (x(0) / x(1))
    Debug.Print x(2)
    x(3) = Round(x(2) * 100, 3)
    Debug.Print x(3)
    Debug.Print Format(x(2), "**.000%")
End Sub

Result:
0.739130434782609
73.913
73.913%

So the numbers work, it's now a question of the Table field definitions and attributes.

I then built my own Table
see jpg

and ran a query

UPDATE Table1 SET Table1.answer = [first]/[second];

Then looked at the values

id	first	second	answer
1	17	23	73.91%

I think you should try Double rather than Decimal based on my test.

Also, I think if you use PERCENT format it is limited to 2 decimal places??? my guess

[June7]

The field (04_PF_FI_pcPass) is set as Decimal field size and Percent format. I have NEVER used either.

I set number fields as either Long Integer or Double. Then deal with formatting in forms and reports.

[D'Anconia]

Well, it doesn't make much sense to me, but when I use Field Size of Double and Decimal places of 3, the datasheet view now shows the correct figures--and then some, showing, in some cases, 10+ decimal places. But, yes - the form I created to display these figures (formatted as percent, 3 decimals) does now display the proper values. I would have liked to have seen those same values in the datasheet view, and I don't know why Access can't handle it as simply as Excel, FoxPro, or Crystal Reports, but so be it. For my purposes, it now works.

I am very grateful to both of you, not only for helping me acheive my goal, but for responding so very quickly!

Thanks very, very much!

[orange]

We are happy to help.
Good luck with your project.

[D'Anconia]

Thanks - and I love your avatar!