Storing a decimal in a table

[DC CS]

Hi all,


I have a form with a subform where data is captured for purchase orders.
The subform has the Items, quantity ordered and the price per unit.

The table where it is stored, I have the PricePer as Number Type and Field Size set to Double.
In the subform, the text field "Format" is "Currency".
Although the user enters a number like "56.87" as the PricePer, the number is stored as "56" with no decimal.

I changed the text format to "Fixed" with the "Decimal Places" to 2, but with the same result.

I am saving the PricePer with this code:
Val(Me!Frm_POsubform.Form!("Price" & i))
In debug mode, it displays as 56 only. The text entered is 56.87.

Any suggestions to fix?
Thanks

[Welshgasman]

Why aren't you using Currency type and not Format?
https://support.microsoft.com/en-us/...9-3418f622e482

[DC CS]

Hi,
Sorry, I a have not made it clear. I am using Currency.

I just tried to save the decimal number as a text into a numeric field in the table, and it is now saving the decimal part as well. Once I convert the string "12.33" with "Val(string)", it rounds the figure down to 12.

Thanks

[Minty]

You should set the number type to Decimal and Format to Currency in your table.
You will almost certainly get rounding errors using a double.

[Welshgasman]

What are your local setting for the decimal point? a comma or an actual period?

You said 'The table where it is stored, I have the PricePer as Number Type and Field Size set to Double.
In the subform, the text field "Format" is "Currency".'


That is NOT the same as setting the field to Currency type.

Look at the link I posted.

[DC CS]

Sorry for the confusion. My terminology not that great.
I meant in the subform, the "Format" property of the TextBox is set to "Currency".



Thanks

[Welshgasman]

Look at the pic. I am talking about the table, NOT the form. Format is just that a format, not a type.