Solved

MS Access Currency Field has no vaue

Posted on 2016-08-07
4
39 Views
Last Modified: 2016-08-08
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.
0
Comment
Question by:Anton Greffrath
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
xtermie earned 500 total points
ID: 41746807
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
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41747389
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
 

Author Comment

by:Anton Greffrath
ID: 41748188
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
 

Author Closing Comment

by:Anton Greffrath
ID: 41748192
Thank you xtermie and Expert Exchange, it is the 5th time that this group came to my rescue in the last 5 months
1

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now