Format field to show 2 decimals

[takara]

I have a multiple items form that shows qty to build for productions orders.



if there are 2 to build it shows 2.000000000000000

that is the data stored in the sql table

I've tried changing the format to standard or #,##.00 and decimal places of the field property to 2

doesn't do anything. Not sure if this is because the table is linked to sql or not, but want to format the data to only show 2 decimals or possibly whole numbers.

[CJ_London]

where are you setting the format? if in a linked? table or query, that format will not be passed to a form or report, you'll need to set it there.

[takara]

I'm setting it on the form.

[June7]

That looks like InputMask property template. Need to set the Format and DecimalPlaces properties.

[takara]

isn't the input mask for input information... my fields already have information and I want them to be displayed properly... I don't see an option in input mask for displaying numbers either.. (figured I would try it)

I have decimal numbers like 2.0000000000000 that are already in the table and I want to display 2 or 2.00

the format and decimal places properties have no effect for some reason

[June7]

Well, Access doesn't hold onto trailing zeros in number fields in tables. But you say this is a linked SQL table? Seems Access is not recognizing it as a number field. Maybe need to convert the value. Try Int() or CDbl().

[takara]

I could convert the value if I was managing the info in VB, but it's just populating the fields on the form from the table, is there a way to convert the number to integer in the properties of a field on a form?

[takara]

your right, when I view the table design via Access (read-only), the data type is short text not integer.

wondering if there is a way to tell the field to handle the text as an integer.

[June7]

I don't know if that can be done when setting the link. I don't have enough experience with SQL. What is the field type in the SQL table (not the link, the actual table)?

[takara]

decimal(38, 20) or decimal precision 38, scale 20

precision is the total number of digits, scale is the number of digits right of the decimal point.

eg. the number 2354.234 has a precision of 7 and a scale of 3

obviously access doesn't recognize this and converts it to short text... can't figure out how to display this properly

[IrogSinta]

You can either use a textbox on the form with the expression:
=Format(VAL(Qty), "#,###.00")
Or
=VAL(Qty) and put the formatting in the Format Property of the textbox.

However if you intend to do any calculations with this qty, you are better off creating a query based on the table and adding a calculated field using VAL(Qty) in the expression. You can then base your form off of this query and you will be able to apply a format property to the new field. This is the more practical route since you may eventually want to create reports and sum your quantity.

[Rawb]

decimal(38, 20)

Your problem is likely related to the fact that Access cannot handle Decimal Numbers with a precision greater than 28. So it's being converted to a text string. I haven't ever tried something like that before, but I would imagine Access would have trouble updating the value as well (since it might be returning a string to SQL Server instead of a number)?

If you're only worried about displaying the number or if you can update it just fine, try change the Format Property of your Form Control to "Fixed" and the Decimal Places Property to "2".

[takara]

IrogSinta,

Thanks so much. I've hidden the original text box that holds the decimal value as a string, and shown a new text box that converts the datatype (val()) as you suggested... works a charm.

the only solution I could think of last night was to create a View in SQL of the data needed and ensure the decimal value was in a format Access could manage (most likely would truncate some precision, but who needs that much anyway)

Rawb,

Not using access to update the tables so this is not a problem. Strictly used to read tables and display and interact with information in a more user specific setting rather than rolling out a NAVISION client to each person (and cheaper).

Changing the format property of the form control to fixed isn't an option as Access doesn't recognize it as an integer.

Thanks for all the responses, problem solved.

[June7]

Bind the control to alias field that converts the text to number.

Or expression in query or textbox ControlSource: Format([fieldname], "0.00")

The value is still text string which means if need to be used in subsequent calcs will still have to convert to number.