Solved

MS Access Currency Field has no vaue

Posted on 2016-08-07
4
50 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 18

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Balance after Repayment - 2 6 58
Microsoft Access VBA - allocate a colour 3 66
Queries: Select, then Append, then Delete 8 33
Database (Access Table) Security Access 8 51
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

738 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