473,574 Members | 2,661 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL money - Access currency conversion

I am having a problem with my Access 2000 front end to a SQL 7 or 2000
database. I create a table in SQL with a field named, say, amt, data
type MONEY. When I link the table in Access using ODBC, the field is
defined as CURRENCY, as expected. Now, if I use SQL to populate the
field with a 3- or 4-decimal amount like 1.234, it appears in the
Access table as 1.23 only. If I expand the decimals to 3 places it
will be 1.230; if the orginal value was 1.236 it will be rounded to
1.240. It seems like Access or ODBC is rounding the SQL MONEY field to
2 decimal places. Both SQL and Access will support 4 decimal places.
If I look at the SQL data using ADO from Access I get 4 decimal places
returned as expected. Can anyone offer a suggestion before I convert
all the fields to REAL to get by this?

Nov 13 '05 #1
4 9417
In Access
Open the table in design view
Accept the warning
Select the field you are concerned with
Change the Decimal places property in the bottom part of the screen to 4

Save and close the table.

--
Terry Kreft
MVP Microsoft Access
"digitalava tar" <rw*****@pcdata partners.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...
I am having a problem with my Access 2000 front end to a SQL 7 or 2000
database. I create a table in SQL with a field named, say, amt, data
type MONEY. When I link the table in Access using ODBC, the field is
defined as CURRENCY, as expected. Now, if I use SQL to populate the
field with a 3- or 4-decimal amount like 1.234, it appears in the
Access table as 1.23 only. If I expand the decimals to 3 places it
will be 1.230; if the orginal value was 1.236 it will be rounded to
1.240. It seems like Access or ODBC is rounding the SQL MONEY field to
2 decimal places. Both SQL and Access will support 4 decimal places.
If I look at the SQL data using ADO from Access I get 4 decimal places
returned as expected. Can anyone offer a suggestion before I convert
all the fields to REAL to get by this?

Nov 13 '05 #2
Does the SQL actually say .1234, or are you generating the SQL somehow. Make
sure the number is actually what you think it is in the SQL text.

On 6 Jan 2005 13:25:12 -0800, "digitalava tar" <rw*****@pcdata partners.com>
wrote:
I am having a problem with my Access 2000 front end to a SQL 7 or 2000
database. I create a table in SQL with a field named, say, amt, data
type MONEY. When I link the table in Access using ODBC, the field is
defined as CURRENCY, as expected. Now, if I use SQL to populate the
field with a 3- or 4-decimal amount like 1.234, it appears in the
Access table as 1.23 only. If I expand the decimals to 3 places it
will be 1.230; if the orginal value was 1.236 it will be rounded to
1.240. It seems like Access or ODBC is rounding the SQL MONEY field to
2 decimal places. Both SQL and Access will support 4 decimal places.
If I look at the SQL data using ADO from Access I get 4 decimal places
returned as expected. Can anyone offer a suggestion before I convert
all the fields to REAL to get by this?


Nov 13 '05 #3
Thank you for your reply but I didn't make myself clear. Access will
show four decimal places, but the last two are zeros, e.g. 123.4500
when SQL has 123.4543. If you try to change the last two in Access and
update, you get the "copy to clipboard" etc etc message. You can get
rid of the message by adding a timestamp to the table but still no four
digits.

I have fixed it by converting the fields in question to real in SQL,
and formatting them (NOT data typing them) as currency in Access.

Nov 13 '05 #4
Thank you for your reply. The SQL data is actually .1234. In fact, if
you retreive the data with ADODB instead of ODBC you get .1234. I
don't have the time to recode everything at this point so I just
converted the data to real and use the Access currency format (not data
type) to display it.

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

24
3508
by: Batista, Facundo | last post by:
Here I send it. Suggestions and all kinds of recomendations are more than welcomed. If it all goes ok, it'll be a PEP when I finish writing the code. Thank you. .. Facundo
12
17875
by: David Garamond | last post by:
What do people recommend for storing money amounts? I've seen people use NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate and why? This is considering various existing currencies, some having low rates (like IDR, in which you can have large amount up to hundreds of trillions) and some high rates (like USD, in which you...
7
2742
by: Philip Nelson | last post by:
Folks, I've been exercising my mind recently about the complexities of implementing a "currency" data type within DB2 to cope with multiple currencies. A monetary value is often simply represented as a DECIMAL column : for example many times I've seen DECIMAL(12,2) used. The issue with this is that there is nothing to interpret what...
2
4830
by: Alberto | last post by:
I have a decimal field in a SQL Server DataBase who stores money and I want to show it in a TextBox and in a ListView controls in C#. If I do: txtMoney.Text = Convert.ToSingle(dr); I see something like: "4.0000" instead "4.00 ?" How can I solve this? Thank you very much
3
2611
by: Jensen bredal | last post by:
Hello gurus, How do i handle Currencies from "asp.net form field " to "insert " querry in database? The "money" is entered in a textbox and my code is falling to the stored procedure due to wrong currency format. Any help will be higly cheered!
16
9490
by: xjohnx | last post by:
Hi, I'm hoping someone can help me I am quite new to Javascript and have had to create a programme which converts dollars into euros and vice versa, here is my script which is working var currency; var amountEntered; currency = window.prompt('Please enter 0 to convert from Dollars to euros and 1 to convert form euros to dollars',''); ...
15
4048
by: soni2926 | last post by:
Hi, I have the following: float.Parse(myproduct.Price.Value.ToString()); myproduct.Price.Value.ToString() returns $24.00 (with the $) Is there anyway to do the above cast, I know the float.Parse(...) doesn't like the $, as i keep getting an invalid cast exception, is there anyway to get this to work? Do i just need to strip out the $...
25
5913
by: mereba | last post by:
Hello My country Ghana is changing its currency. I want to write a small programme in C++ that can covert from the old currency into the new one. I would like this programme to run behind a simple calculator- looking interface. I'm quite new to C++, and I would need some help. Please any suggestion is welcome :) The following is what I...
9
4928
by: bb | last post by:
Hi, Are there any open source data types ( c++ classes ) for Money and/or Price that I can inherit and customize for my use instead of writing from the scratch? Any help would be much appreciated. Thanks.
0
7815
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8078
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
6477
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5634
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3755
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3766
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1076
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.