×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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.

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

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

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

(OP)
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.


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

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

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

(OP)
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.

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

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

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

(OP)
That is a good idea. Thank you.

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close