Thread: Float Percision with MS Access 97
If I have a table with a float4 and it has greater precision that 4 to the left of the decimal point, Access will not let me change that record and gives me a write conflict. I have troubleshooted the problem to the point that Access when changing a record checks all known fields as part of the update statement (even though it has, and knows it has, the index). For example: if I have a table with the following Parts: PartID Name Price Count 555 Tubing 2.50 101.123456 MS Access seems to get the count from the ODBC driver as 101.1234. When you build a screen that has all of the fields in the then change Price Access does the following: UPDATE Parts SET Price = 2.75 WHERE PartID = 555 AND Name = 'Tubing' AND Price = 2.5 AND Count = 101.1234; This causes a "write conflict" because the counts are not equal. Is there are way to get more precision from the ODBC driver or am I missing a setting somewhere? Thanks in advance, Michael Calabrese Bike Friday m2calabr-AT-yahoo-DOT-com __________________________________________________ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
Michael Calabrese <m2calabr@yahoo.com> writes:
> If I have a table with a float4 and it has greater
> precision that 4 to the left of the decimal point,
> Access will not let me change that record and gives me
> a write conflict.
You do realize that float4 only carries about six decimal digits of
precision on most machines?
> For example: if I have a table with the following
> Parts:
> PartID Name Price Count
> 555 Tubing 2.50 101.123456
> MS Access seems to get the count from the ODBC driver
> as 101.1234.
And did well to get that, rather than 101.123.
Try float8 or numeric if you want more digits.
regards, tom lane
I am sorry, but I guess I did not make my question clear. I am not looking for greater backend precision (ie moving to float8). There seems to be a mis-comunication between Access and postgres with float4. Access is seeing the number with fewer digits than want must be in postgres. If I do a : UPDATE Parts SET Count = round(Count,4); Then the access query from my first email will work. This is a pain right now because my users will get a write conflict error, call me, I will find the offending field, do and Update with the round(x,4), then it will work. I could fix it by putting round(x,4) with all float fields, but this seems like a poor fix. __________________________________________________ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
Here is a quote from someone else's email
>>>
Are you using Row Versioning? This uses the xmin field so that it will be
necessary to relink the tables. MS Access can then use the primary key and
xmin to identify a row as exactly the same row it earlier retrieved. If you
don't do this access does uses all the fields (bar memo and BLOB's) in the
where condition of the update. This can lead to precision problems with
floats.
You will also need this in the back end to allow comparison of int4 to xid:
create function int4eq(xid,int4)
returns bool
as ''
language 'internal';
create operator =
leftarg=xid,
rightarg=int4,
procedure=int4eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);
<<<
This does fix the problem. The trouble is that access compares each field to
determine which record to update when really all it needs is the oid.
Relinking with row versioning fixed my problem. I don't know what to say if
you need creater precision... could you use numeric data type for really big
numbers?
respectfully,
Joseph
=============
-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Michael Calabrese
Sent: Monday, August 20, 2001 6:03 PM
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Float Percision with MS Access 97
I am sorry, but I guess I did not make my question
clear. I am not looking for greater backend precision
(ie moving to float8). There seems to be a
mis-comunication between Access and postgres with
float4.
Access is seeing the number with fewer digits than
want must be in postgres. If I do a :
UPDATE Parts SET Count = round(Count,4);
Then the access query from my first email will work.
This is a pain right now because my users will get a
write conflict error, call me, I will find the
offending field, do and Update with the round(x,4),
then it will work.
I could fix it by putting round(x,4) with all float
fields, but this seems like a poor fix.
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
I use Row Versioning to get around this (my thanks to who ever put that in). You'll need to re-link as it adds in the xmin field. Access will then use the Primary Key and xmin in its where clause to identify the row. Hope this helps, - Stuart > -----Original Message----- > From: Michael Calabrese [SMTP:m2calabr@yahoo.com] > Sent: Monday, August 20, 2001 11:03 PM > To: pgsql-odbc@postgresql.org > Subject: Re: Float Percision with MS Access 97 > > I am sorry, but I guess I did not make my question > clear. I am not looking for greater backend precision > (ie moving to float8). There seems to be a > mis-comunication between Access and postgres with > float4. > Access is seeing the number with fewer digits than > want must be in postgres. If I do a : > UPDATE Parts SET Count = round(Count,4); > Then the access query from my first email will work. > This is a pain right now because my users will get a > write conflict error, call me, I will find the > offending field, do and Update with the round(x,4), > then it will work. > > I could fix it by putting round(x,4) with all float > fields, but this seems like a poor fix. > > > __________________________________________________ > Do You Yahoo!? > Make international calls for as low as $.04/minute with Yahoo! Messenger > http://phonecard.yahoo.com/