On Mon, 17 Nov 2008 20:52:23 +0200, Bruce R. Roland
John
FWIW I have run into and found that an appropriate sounding width for
numeric data fields in a database always seem to give me trouble if I do
not allow at least an extra width of 1 to accommodate the decimal point.
I can't remember where it's documented and it is so well hidden that I
can't find it now but it is there somewhere. <g> The length needs to
include the decimal point and the minus sign if one is likely to store
negative values ie to store values between -999.99 and 9999.99 the field
needs a length of at least 7 with 2 decimals. Back in the days of dBase
II when data was stored on 135kb floppy disks it was important to optimise
table size but today when entry-level harddrives start in the Gb range I
never bother to change the default length of 10 for numeric fields unless
I'm dealing with BIG numbers or if I was to expect millions of records.
(Never had one of those yet. <g>)
For example you stated that you were using this numeric field to store a
percentage with two decimals (5,2). That would presumably allow for
three(3) whole digits and the two decimal values (i.e. 100.00 for
100%). However in my experience DBase will have problems with the value
100.00 in a field with a width of 5,2 because of the decimal (it needs
its' own space) and so rather than 5,2 for 100.00 I need to make my
field width 6,2 which then works fine.
I have seen dbase also display a number such as 100.00 as "**.**" when I
use the 5,2 width instead of the 6,2 width as well.
dBase will store -100.00 in a n(5,2) field as -100 and it will store
100.00 as 100.0 without throwing an error or resorting to asterisks.
In an earlier reply to John I noted that I couldn't reproduce the
translate error that he was experiencing. Since then I've been playing
with this a bit more and I managed to generate the error by trying to save
a value that was substantially too big for the field and the field was too
small to hold the value in scientific notation. I guess that's what the
translation error is about.
If a number is too large for the field, dBase will either store it as
asterisks, in scientific notation or if can't use one of those it throws
the "Database Engine Error: Translate Error. Value out of bounds"
error. I haven't, however, been able to pin down the boundaries as I
don't have the time at the moment to tackle this methodically but it
looks as if dBase will only use scientific notation if the field length is
7 or greater. I was able to generate the asterisks by using a value that
was too big for the field but I haven't been able to determine at what
point dBase will change from asterisks to the error message.
The answer to the whole problem is to make sure the field is big enough in
the first place. <g>
Mervyn.