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
Solved

MS Access Currency Field has no vaue

Posted on 2016-08-07
4
44 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 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

792 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