469,270 Members | 1,174 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

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 9197
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
"digitalavatar" <rw*****@pcdatapartners.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.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, "digitalavatar" <rw*****@pcdatapartners.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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

24 posts views Thread by Batista, Facundo | last post: by
12 posts views Thread by David Garamond | last post: by
2 posts views Thread by Alberto | last post: by
3 posts views Thread by Jensen bredal | last post: by
15 posts views Thread by soni2926 | last post: by
25 posts views Thread by mereba | last post: by
9 posts views Thread by bb | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.