MS Access Currency Field has no vaue

I am in the process of converting my MS Access 2016 Database (Back End portion) to SQL Server. Everything went fine so far except for one challenge I cannot resolve.

My conversion started by converting the existing MS Access database to SQL (no problem) but when I display the converted data (which is in SQL Server format) on an MS Access Form, all the Currency fields, where there is a currency value present, display  as null (No Value). the Currency fields where the value in SQL Server is 0 (Zero) displays correctly.

If I add a monetary value to the (null) Currency field, the value disappears immediately, the same happens if I change a Zero value Currency field to a Currency value greater than 1.

These fields are defined as Money fields on the SQL Server database and on the MS Access ODBC Linked table it (automatically) becomes Currency.
Anton GreffrathAsked:
Who is Participating?
 
xtermieConnect With a Mentor Commented:
it could be a regional settings thing with currency symbols decimals etc that converts values to text and then appear empty
A similar problem with a workaround is described in the link below, it may help you
https://bytes.com/topic/access/answers/206192-upsizing-currency-fields
0
 
Craig YellickDatabase ArchitectCommented:
Could be a formatting issue in the Access form. Can you see the correct values in a datasheet view when displayed directly from the linked table?  Trying creating a new form from scratch and ensure that the text controls apply no formatting to the field.

-- Craig
0
 
Anton GreffrathAuthor Commented:
Thanks xtermie, I checked the article and changed the Regional Settings in the ODBC link accordingly. It worked and now the Currency values are displayed on the MS Access side. I don't know how I would ever have found out this information on my own.
For those who are interested, the 'Regional Setting' option when the ODBC link is created, must remain unchecked. I thought that it might affect the notorious Date formats, but thank goodness, it did not.
1
 
Anton GreffrathAuthor Commented:
Thank you xtermie and Expert Exchange, it is the 5th time that this group came to my rescue in the last 5 months
1
All Courses

From novice to tech pro — start learning today.