SOLVED

In SharePoint List - Use Thousands Separator = NO is not displayed in Access tables linked to list

%3CLINGO-SUB%20id%3D%22lingo-sub-2738245%22%20slang%3D%22en-US%22%3EIn%20SharePoint%20List%20-%20Use%20Thousands%20Separator%20%3D%20NO%20is%20not%20displayed%20in%20Access%20tables%20linked%20to%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2738245%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20an%20Office%20365%20user.%26nbsp%3B%20I%20am%20working%20with%20an%20ACCESS%20database%20with%20tables%20linked%20to%20SharePoint%20lists.%26nbsp%3B%20In%20the%20SharePoint%20lists%20I%20have%20used%20the%20recently%20added%20option%20of%20turning%20off%20the%20Use%20Thousands%20Separator%20setting%20under%20More%20Options.%26nbsp%3B%20The%20numbers%20appear%20correctly%20without%20the%20thousands%20seperator%20in%20SharePoint%20but%20the%20numbers%20in%20the%20corresponding%20fields%20in%20the%20linked%20tables%20in%20ACCESS%20still%20show%20the%20comma%20thousands%20separator.%3C%2FP%3E%3CP%3EI%20have%20tried%20refreshing%20links%2C%20changing%20linked%20tables%20in%20ACCESS%20to%20local%2C%20renaming%20them%2C%20and%20re-adding%20the%20SharePoint%20list%20as%20a%20new%20external%20source%20and%20it%20is%20not%20working.%26nbsp%3B%20ACCESS%20won't%20let%20you%20change%20a%20number%20format%20in%20the%20linked%20table.%26nbsp%3B%20Any%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2738245%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2741967%22%20slang%3D%22en-US%22%3ERe%3A%20In%20SharePoint%20List%20-%20Use%20Thousands%20Separator%20%3D%20NO%20is%20not%20displayed%20in%20Access%20tables%20linked%20to%20li%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2741967%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20assuming%20that%20you%20will%20need%20to%20use%20either%20a%20query%2C%20or%20form%20for%20manipulating%20the%20data%2C%20or%20report%20for%20reporting%20purposes%2C%20instead%20of%20working%20directly%20within%20the%20tables.%20In%20any%20of%20three%20cases%3B%20you%20can%20use%20the%20Format%20property%20of%20the%20field%20in%20a%20query%20(1)%2C%20or%20the%20Format%20property%20of%20the%20textbox%20in%20a%20form%20or%20report%20(2)%2C%20by%20setting%20it%20to%20%22General%22%20or%20%22Fixed%22%2C%20then%20set%20the%20Decimals%20property%20to%20the%20digits%20desired.%20Just%20to%20remind%2C%20Format%20never%20modifies%20the%20actual%20number%20data%2C%20it%20just%20changes%20the%20way%20how%20the%20data%20displayed%20to%20the%20user%20on%20the%20screen%20or%20on%20the%20report.%3CBR%20%2F%3EIf%20none%20of%20these%20are%20helpful%2C%20can%20you%20please%20share%20more%20details%20about%20what%20do%20you%20want%20to%20do%20with%20the%20data%20in%20MS%20Access.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2742594%22%20slang%3D%22en-US%22%3ERe%3A%20In%20SharePoint%20List%20-%20Use%20Thousands%20Separator%20%3D%20NO%20is%20not%20displayed%20in%20Access%20tables%20linked%20to%20li%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2742594%22%20slang%3D%22en-US%22%3EBased%20on%20a%20discussion%20of%20this%20problem%20on%20MS%20Answers%2C%20I%20believe%20the%20only%20option%20is%2C%20as%20you%20suggest%2C%20formatting%20in%20a%20query%20or%20control%20on%20a%20form%20or%20report.%3C%2FLINGO-BODY%3E
Occasional Contributor

I am an Office 365 user.  I am working with an ACCESS database with tables linked to SharePoint lists.  In the SharePoint lists I have used the recently added option of turning off the Use Thousands Separator setting under More Options.  The numbers appear correctly without the thousands seperator in SharePoint but the numbers in the corresponding fields in the linked tables in ACCESS still show the comma thousands separator.

I have tried refreshing links, changing linked tables in ACCESS to local, renaming them, and re-adding the SharePoint list as a new external source and it is not working.  ACCESS won't let you change a number format in the linked table.  Any suggestions?

3 Replies
best response confirmed by Merlin Siefken (Occasional Contributor)
Solution

I am assuming that you will need to use either a query, or form for manipulating the data, or report for reporting purposes, instead of working directly within the tables. In any of three cases; you can use the Format property of the field in a query (1), or the Format property of the textbox in a form or report (2), by setting it to "General" or "Fixed", then set the Decimals property to the digits desired. Just to remind, Format never modifies the actual number data, it just changes the way how the data displayed to the user on the screen or on the report.
If none of these are helpful, can you please share more details about what do you want to do with the data in MS Access.

Based on a discussion of this problem on MS Answers, I believe the only option is, as you suggest, formatting in a query or control on a form or report.