How to prevent "17.0" from rounding to "17" in a table?

[taiwai94]

I have created a table to store various values some of which are numbers. The field in question is setup as follows:

Data Type = Number
Field Size = Decimal
Precision = 28
Scale = 14
Decimal Places = Auto

This field stores numbers of a scientific origin, and as far as I know, the above setup is the only that will allows values ranging from trillions to trillionths.

In a particular case, I enter '17.0' into the field which is then rounded to simply '17'. In a purely numerical case 17 and 17.0 are the same, but when it comes scientific data, 17.0 is more accurate than 17 so I need the .0 to appear exactly as I type it in.

Does anyone know how to stop the access table and field from dropping numbers than end in .0 (or even .0000 etc).

Regards,

David

 

[PHV]

Simply use an appropriate display format: 0.0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[taiwai94]

Hi PHV,

The problem is such a variety of numbers, such as some numbers having no decimal places and others having one, two or several. The numbers need to appear in Access exactly as they are ie, 3770 must appear as 3770 and not 3770.0, 121.0 as 121.0 and not 121, 53.57 as 53.57 and not 53.5700, and so on etc. I have tried all of the display format and even had a try at a custom one all to no avail.

I know if I convert the fields to text, whatever is typed will appear exactly as it is typed, but this creates serious problems that if Access treats the numbers like text, they can no longer be used for calculations, graphs, and ascending/descending value sorting etc.

Hence, my dilema.

Regards,

David

 

[earthandfire]

The problem is that the numbers internally are not stored as we see them, they are stored in binary format and unless they are integers, it is a special IEEE floating point format. As such there is no way to differentiate a floating point number of 3770 from 3770.0.

If you know the precise display format for each number you could have two fields, a numeric field to store the value for calculation purposes and a text field to store the number for display purposes.

Not a very satisfactory solution, but I'm afraid I can't think of a better one.

Hope this helps.

 

[SkipVought]

Just to pile on...

the problem is NOT that there is "rounding" going on.

if the value is

2.012345678

It might be FORMATTED to DISPLAY

2
2.0
2.01

but the VALUE is STILL 2.012345678

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[earthandfire]

It may not even be 2.012345678, that may just be the nearest decimal number that it can be converted to, whereas as within a calculation it may be marginally more precise. This is why we sometimes see -0, which of course makes no sense to us.

 

[SkipVought]

Ahhhhhh...

the DIFFERENCE between ACCURACY and PRECISION!

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[earthandfire]

Who'd be a programmer?

 

[taiwai94]

earthandfire,

If I were to change the field in question to text, then what you see is exactly what you get, but within the table it is no longer possible to sort the field with regards to numerical values, i.e. in a text field 100 comes before 2 etc.

Is there anyway to store the numbers in a text field, but use a query or form to recognise the numbers as numbers rather than text, so they behave like numbers (so that 2 comes before 100 rather than the other way around as mentioned above)?

Regards,

David

 

[earthandfire]

Not really. The only solution that I can think of (not overly clever though) is to have an additional field. Keep your numeric field for sorting /calculations etc., and have a text field for display purposes.

 

[jsteph]

taiwai94
You could do ORDER BY Cdbl(TEXTFIELD)

This will slow things down, and you'll want to ensure that all values are indeed numeric, though I think this will just sort nulls at the top.
--Jim

 

[SilentAiche]

taiwai94,

Welcome to Tek-Tips!

Try setting your field format to [blue]0.0#####[/blue].

All integers will then appear with .0 appended to them (i.e., 17 appears as 17.0). Also, 17.5614 will appear as 17.5614. Add as many #s in the format as you may possibly need (the last decimal place will round if you enter more digits than you have #s in the format).

Note this will NOT allow you to have 17 appear instead of 17.0. If you need both, then earthandfire may have the best solution - have a text field and a numeric field (I think you can enter the value as text, and then use =Text*1 to convert to a numeric value in the "number" field).

Hope this helps!
Tim


[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[bamageek]

why not just set Format to Standard
and Decimal places to 1.

this wouldn't force entry as 17
but is still stored/shown as 17.0

if I am understanding the problem correctly.

 

[PHV]

to sort the field with regards to numerical values
ORDER BY Val([your field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[taiwai94]

SilentAiche,

What you have proposed seems to work. Convert the table field to text to overcome the decimal place problem. In a query, using the expression "QueryField: [TableField]*1" will "convert" the text back to numerical behaviour. This does mean that the query has 2 fields - one with the propert text value and one with the numerical behaviour for sort-order purposes. However, it is more desirable to have the same field twice in a query than a table, especially when it comes to updating figures.

Thank-you to all who have tried to help me out with this little problem, but I think I can call this case to a close.

Thanks again to everyone.

Regards,

David

 

[mrstnturner]

have you tried not letting it default to "auto" under the decimal paramenter, maybe changing it there would fix the problem.

 

[lameid]

Unless you are runnning Jet on your local hard drive, not having the second field will created horrid performance problems. The easiest way to fix this is to force data entry on a form and use an afterupdate event on the control that contains text to update a textbox that contains the numeric field. I'm curious as to why you want to store this information in access when accuracy will not make any differnece in the arithmetic Access does.

While I'm commenting, I've used the Eval function to avoid floating point errors... I had to use a recrodset to programatically step through a recordset to build a string (text) that I could pass to Eval rather than performing the series of arithmetic.

All that said, I have seen a company targeting statistical analysis in access. Can't say I've done anything but read about their products from time to time but you may be interested:

http://www.fmsinc.com/products/statistics/index.html

 

[taiwai94]

Hi lameid,

I'm not too worried about the performance as this access database will only ever be used by one person at any one time on any one PC (more specifically, me on my own PC).

Accuracy may not make a difference in access, but these numbers represent scientific data in which 121 and 121.0 are not the same (121.0 indicates greater reliability, or less scientific/experimental error).

Also, the calculations in access are not going to be all that complicated. The main purpose of wanting the field values to behave as numbers is for sorting purposes i.e. list items in ascending/descending value. The calculations will be fairly simple too, such as "(Field1-Field2)/Field2"

If anything, this is mostly a database just to store data and extract or arrange as and when I should need it with a bit of 'neat presentation' via a report. Thanks anyway.

mrstnturner - I did have the number originally set to "Auto" but it made no difference.

 

[taiwai94]

With regards to rounding:

Is it possible to use a field to define the rounding of another field in a query?

For example, in a query, field B is rounded to the number of decimal places specified in field A? Can this be done, or something similiar?

Thanks.

 

[PHV]

RoundedValue: Round([fieldB], [fieldA])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886