My DB is using SQL tables as the substance behind my FM Layout report.
What I'm running into are some number fields that have numbers in them
such as 72.00 or 8.665, but they come out in FM as 71.9999999999 and
8.6649999999999.

If my numbers were always going to be a fixed number of decimal places
I would either use round or set the number format to the appropriate
setting and it would go away. Unfortunately the # of decimal places
used in this particular field varies.

The real odd thing is the data entered into these SQL tables literally
is 72.00 or 8.665, it just appears to be some oddity in the way FM
pulls it from the SQL tables that it displays oddly. And it doesn't do
it to every number in the table, some come through just fine.

Any ideas on a function that might magically make this go away?

Doug Anderson wrote:
> My DB is using SQL tables as the substance behind my FM Layout report.
> What I'm running into are some number fields that have numbers in them
> such as 72.00 or 8.665, but they come out in FM as 71.9999999999 and
> 8.6649999999999.
>
> If my numbers were always going to be a fixed number of decimal places I
> would either use round or set the number format to the appropriate
> setting and it would go away. Unfortunately the # of decimal places
> used in this particular field varies.
>
> The real odd thing is the data entered into these SQL tables literally
> is 72.00 or 8.665, it just appears to be some oddity in the way FM pulls
> it from the SQL tables that it displays oddly. And it doesn't do it to
> every number in the table, some come through just fine.
>
> Any ideas on a function that might magically make this go away?
>


set the number format on the display field to a fixed number of decimal
points (option only available if the field is actually a number type)


Chris

On 2007-08-01 19:56:53 -0500, Chris Brown
<cbrown [at] medicine.adelaide.edu.au> said:

> Doug Anderson wrote:
>> My DB is using SQL tables as the substance behind my FM Layout report.
>> What I'm running into are some number fields that have numbers in them
>> such as 72.00 or 8.665, but they come out in FM as 71.9999999999 and
>> 8.6649999999999.
>>
>> If my numbers were always going to be a fixed number of decimal places
>> I would either use round or set the number format to the appropriate
>> setting and it would go away. Unfortunately the # of decimal places
>> used in this particular field varies.
>>
>> The real odd thing is the data entered into these SQL tables literally
>> is 72.00 or 8.665, it just appears to be some oddity in the way FM
>> pulls it from the SQL tables that it displays oddly. And it doesn't do
>> it to every number in the table, some come through just fine.
>>
>> Any ideas on a function that might magically make this go away?
>>
>
>
> set the number format on the display field to a fixed number of decimal
> points (option only available if the field is actually a number type)
>
>
> Chris

That won't work because some data needs to be more than two decimal
places with a minimum of two decminal places for all numbers. So if
someone enters a 1 it should display as 1.00, but if someone enters
1.865 it should display as 1.865, and if someone enters 1.3216 it
should come out as 1.3216. If I force the # of decminal places to two
then 1.865 will display as 1.87 and 1.3216 will come out as 1.32,
neither of which is what I want. If I set the decimal places to 5 then
I get 1.00000, 1.86500 and 1.32160. I don't want to show those extra
0's.

In article <2007081409495116807-dougcpa [at] maccom>, Doug Anderson
<dougcpa [at] mac.com> wrote:

> On 2007-08-01 19:56:53 -0500, Chris Brown
> <cbrown [at] medicine.adelaide.edu.au> said:
>
> > Doug Anderson wrote:
> >> My DB is using SQL tables as the substance behind my FM Layout report.
> >> What I'm running into are some number fields that have numbers in them
> >> such as 72.00 or 8.665, but they come out in FM as 71.9999999999 and
> >> 8.6649999999999.
> >>
> >> If my numbers were always going to be a fixed number of decimal places
> >> I would either use round or set the number format to the appropriate
> >> setting and it would go away. Unfortunately the # of decimal places
> >> used in this particular field varies.
> >>
> >> The real odd thing is the data entered into these SQL tables literally
> >> is 72.00 or 8.665, it just appears to be some oddity in the way FM
> >> pulls it from the SQL tables that it displays oddly. And it doesn't do
> >> it to every number in the table, some come through just fine.
> >>
> >> Any ideas on a function that might magically make this go away?
> >
> > set the number format on the display field to a fixed number of decimal
> > points (option only available if the field is actually a number type)
>
> That won't work because some data needs to be more than two decimal
> places with a minimum of two decminal places for all numbers. So if
> someone enters a 1 it should display as 1.00, but if someone enters
> 1.865 it should display as 1.865, and if someone enters 1.3216 it
> should come out as 1.3216. If I force the # of decminal places to two
> then 1.865 will display as 1.87 and 1.3216 will come out as 1.32,
> neither of which is what I want. If I set the decimal places to 5 then
> I get 1.00000, 1.86500 and 1.32160. I don't want to show those extra
> 0's.

I don't know anything about SQL, but if you havent already, you could
try making the field used to store / display the numbers into a Text
field instead of a Number field - on either the SQL table side or
FileMaker side, or even both sides. It may not help since FileMaker
doesn't really care that much about the field definitions of Number and
Text.

It may be a bug in FileMaker that you have to live with until an update
comes out to fix it. :o(

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

In article <160820071449494411%helpful_harry [at] nom.de.plume.com>,
Helpful Harry <helpful_harry [at] nom.de.plume.com> wrote:

> In article <2007081409495116807-dougcpa [at] maccom>, Doug Anderson
> <dougcpa [at] mac.com> wrote:
>
> > On 2007-08-01 19:56:53 -0500, Chris Brown
> > <cbrown [at] medicine.adelaide.edu.au> said:
> >
> > > Doug Anderson wrote:
> > >> My DB is using SQL tables as the substance behind my FM Layout report.
> > >> What I'm running into are some number fields that have numbers in them
> > >> such as 72.00 or 8.665, but they come out in FM as 71.9999999999 and
> > >> 8.6649999999999.
> > >>
> > >> If my numbers were always going to be a fixed number of decimal places
> > >> I would either use round or set the number format to the appropriate
> > >> setting and it would go away. Unfortunately the # of decimal places
> > >> used in this particular field varies.
> > >>
> > >> The real odd thing is the data entered into these SQL tables literally
> > >> is 72.00 or 8.665, it just appears to be some oddity in the way FM
> > >> pulls it from the SQL tables that it displays oddly. And it doesn't do
> > >> it to every number in the table, some come through just fine.
> > >>
> > >> Any ideas on a function that might magically make this go away?
> > >
> > > set the number format on the display field to a fixed number of decimal
> > > points (option only available if the field is actually a number type)
> >
> > That won't work because some data needs to be more than two decimal
> > places with a minimum of two decminal places for all numbers. So if
> > someone enters a 1 it should display as 1.00, but if someone enters
> > 1.865 it should display as 1.865, and if someone enters 1.3216 it
> > should come out as 1.3216. If I force the # of decminal places to two
> > then 1.865 will display as 1.87 and 1.3216 will come out as 1.32,
> > neither of which is what I want. If I set the decimal places to 5 then
> > I get 1.00000, 1.86500 and 1.32160. I don't want to show those extra
> > 0's.
>
> I don't know anything about SQL, but if you havent already, you could
> try making the field used to store / display the numbers into a Text
> field instead of a Number field - on either the SQL table side or
> FileMaker side, or even both sides. It may not help since FileMaker
> doesn't really care that much about the field definitions of Number and
> Text.
>
> It may be a bug in FileMaker that you have to live with until an update
> comes out to fix it. :o(
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

According to FileMaker experts at the FileMaker Devcon this year,
FileMaker stores all data internally as text. I suppose the field
definition determines how FileMaker interprets, displays and sorts the
text.

If this is true, then making it a text field might cure the problem.

--
For email, change <fake> to <earthlink>
Bill Collins