MS Access Linked to SQL Server Numeric Fields Lose Precision and Scale 1

[kentover]

I have an MS Access 2013 mdb linked to a SQL Server 2012 DB. There are tables with NUMERIC(18, 2) fields in SQL Server. I link to those and the linked tables show the correct precision and scale in the design view but not when you view the data. It drops the zeros off of the decimal places. So 115.10 shows up as 115.1 and 115.00 shows up as 115. They are supposed to be money fields but the end user does not want the dollar sign. Linked views of the same data is doing the same thing. Access is not consistently showing two decimal places.

Also, one of those money columns is showing up in Access with the dollar signs even though it is setup the same as all the other NUMERIC(18, 2) fields. It always has two decimal places so it is being interpreted as currency.

I am linked with the DSN of SQL Server 11.

Thanks in advance for your help.

 

[dhookom]

Viewing of data should be through forms and reports where you can set the Format and Decimals properties of the controls displaying the values. Is this what you have set but are still having issues?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[kentover]

It is for an end user doing read-only analysis. He is writing queries and creating formulas. A form or report would be ideal for me but not for his purposes. He is an actuary.

 

[dhookom]

Did you attempt to set the format in the design view of the linked tables?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[kentover]

It is read-only and cannot be altered in Access. The data types are correct in Access, They are just not displaying correctly. In the tables and views Access is dropping zeros instead of showing two decimal places (115.1 instead of 115.10). Also in the view one NUMERIC(18,2) field is showing a dollar sign and keeping both decimal places. That field is setup the same as the others but behaving differently.

 

[dhookom]

I have never seen trailing zeros display without setting the format of field/column or control.

I just went to the design view of a linked SQL Server table and set the format of a couple numeric fields to Standard with 2-4 decimals. When I viewed the table in datasheet, the appropriate number of decimal places appeared.

If you can't change the display format for a linked SQL table consider creating a query from the table and setting properties.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[kentover]

That is a good idea. Thank you.

 

[kentover]

That worked well. I created the query and set the fields format as Standard and two decimal places. Thanks again Duane.