Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
oepirobo
Starting Member
36 Posts |
 Posted - 2003-03-20 : 11:37:00
|
| I have a table with a real type field which I use to save percentages. In access I have a form that captures this field. The problem is that if I type 4% on this field the database will save 3.999999E-2, or at least that what gets shown with query analyser. When I open it with enterprise manager I see the data as 0.04, but the access form won't let me save anything else on the record that has this field. I get "Write conflict". I have another record which shows up on every screen as 0.02 which is good and everything works fine in the access form. Why the difference? and how come if I change the value in enterprise manager to 0.04 it still gets shown by query analyser as 3.999999E-2? |
|
|
oepirobo
Starting Member
36 Posts |
Posted - 2003-03-20 : 12:05:38
|
| I just found out that when using a real type field in SQL Server if you save a value greater than or equal to 0.0375 and lower than 1 it is automatically saved with 8 decimal places. Now all I need is to figure out how to fix this so that it only saves up as many decimals as I tell it to. |
 |
|
|
oepirobo
Starting Member
36 Posts |
Posted - 2003-03-20 : 14:33:33
|
| I tried changing the field from real to numeric but the problem stays the same, although I've noticed that the access form lets me save changes on all fields except one which is type text. If I try to change the data on this field I still get "Write conflict".What to do?Edited by - oepirobo on 03/20/2003 14:45:19 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-20 : 16:11:11
|
| Don't use the real field ... just use the currency or money datatype and always format your data as a percent. It has 4 decimal places of accuracy.Are you storing this data in Access or SQL? Sounds like SQL .. if that is the case, use the numeric() or decimal field types. But as you mentioned, Access has problems reading those fields and inteprets them as text or floats or reals.Reals and floats do not store numbers exactly; it is a little out of the scope of this forum to explain exactly how they work but I try to avoid them whenever possible.remember not to confuse the formatting of your data with what is actually STORED ... you can always format the data as you like (add zeroes, etc) and any other client that reads your data is welcome to format it as they like, it is the SQL Server's job just to STORE the data accurately.- Jeff |
|
|
|
oepirobo
Starting Member
36 Posts |
Posted - 2003-03-20 : 17:13:54
|
| It works... I changed the type from real to money in SQL Server and the problem disappeared, but I thought that it was a bit sloppy to use a money field to save a percent value so I changed it again to a numeric field with the same precision and scale of the money type (not that I think it makes a difference) and somehow it works now. Thanks. |
|
|
|
|
|
|
Real type field