SQL money - Access currency conversion

[digitalavatar]

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?

[Terry Kreft]

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?

[Steve Jorgensen]

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?

[digitalavatar]

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.

[digitalavatar]

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.