Create table -> Numeric fields

[ingrid]

I have a table with some numeric fields with property double precision with 2 decimals. When using a table create query the property in the new table is double precision with automatic decimals. Now 3,40 becomes 3,40335162067604. How can I prevent this from happening?

 

[neileg]

Sounds like your original data is formatted to 2 decimals, but the stored value is the full precision. If you truly only want to store to 2 decimal places, use the Round() function in your make table query.

 

[Pat Hartman]

Actually, you'll find that using the currency data type (even for non-money items) when you don't need more than 4 positions of precision will save lots of problems with calculations. Currency is actually a long integer that is scaled to a fixed 4 decimal positions. This means that you won't run into stupid floating point errors in simple calculations.

 

[ingrid]

Sounds like your original data is formatted to 2 decimals, but the stored value is the full precision. If you truly only want to store to 2 decimal places, use the Round() function in your make table query.

Round() doesn't seem to work in access 97.

 

[neileg]

Round() doesn't seem to work in access 97.

Are you sure? I use A2k, now so can't check, but I'm sure that ROUND() was available in A97.

 

[Rich]

Function Roundx(Value, Optional Precision As Variant) As Double

If IsNull(Value) Then Exit Function
If IsMissing(Precision) Then Precision = 2
Value = Fix(Value * 10 ^ Precision + 0.5 * Sgn(Value)) / 10 ^ Precision
Roundx = Value

End Function

 

[ingrid]

Function Roundx(Value, Optional Precision As Variant) As Double

If IsNull(Value) Then Exit Function
If IsMissing(Precision) Then Precision = 2
Value = Fix(Value * 10 ^ Precision + 0.5 * Sgn(Value)) / 10 ^ Precision
Roundx = Value

End Function

Thanks, but this isn't possible. I need to do it in a SQL-statement.

 

[Rich]

Having created the function you can then use it in your query

Total:Roundx([YourField])

 

[RV]

Are you sure? I use A2k, now so can't check, but I'm sure that ROUND() was available in A97.

Nope, it isn't.

RV

 

[ingrid]

It works.
Thanks!!!