Solved

MS Access Currency Field has no vaue

Posted on 2016-08-07
4
32 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

13 Experts available now in Live!

Get 1:1 Help Now