Control Source calculation error

[Joshua J]

Hi there,

Just a bit confused at the moment & would love some help .

All other fields in this table are ok except this one.

I'm no access genius, I was reluctant to pay $15000 for a weighbridge
program so I made one myself.

The control source is as follows for the Trailer 1 Nett field,
=[Trailer 1 Gross]-[Trailer 1 Tare]
The numbers(& result) are,
41.08-34.28 = 6.800003 (should display as just 6.8)

It just seems to be ignoring all the decimal formats (set to 2) we have
place which are working perfectly everywhere else except for this 1 record.

Thanks in advance.

 

[fredg]

Hi there,

Just a bit confused at the moment & would love some help .

All other fields in this table are ok except this one.

I'm no access genius, I was reluctant to pay $15000 for a weighbridge
program so I made one myself.

The control source is as follows for the Trailer 1 Nett field,
=[Trailer 1 Gross]-[Trailer 1 Tare]
The numbers(& result) are,
41.08-34.28 = 6.800003 (should display as just 6.8)

It just seems to be ignoring all the decimal formats (set to 2) we have
place which are working perfectly everywhere else except for this 1 record.

Thanks in advance.

=Format([Trailer 1 Gross]-[Trailer 1 Tare],"#.00")

Will return 2 decimals.

 

[John W. Vinson]

Hi there,

Just a bit confused at the moment & would love some help .

All other fields in this table are ok except this one.

I'm no access genius, I was reluctant to pay $15000 for a weighbridge
program so I made one myself.

The control source is as follows for the Trailer 1 Nett field,
=[Trailer 1 Gross]-[Trailer 1 Tare]
The numbers(& result) are,
41.08-34.28 = 6.800003 (should display as just 6.8)

It just seems to be ignoring all the decimal formats (set to 2) we have
place which are working perfectly everywhere else except for this 1 record.

Thanks in advance.

It appears that you're using a Single Float size number. Floating point
numbers, by their nature, are approximations - a single float is accurate to
about 7 decimal places. Just as the fraction 1/7 cannot be represented exactly
as a decimal number, so the fraction 1/100 (or 1/10) cannot be represented
exactly as a binary fraction - and therefore not as a floating point number.

I'd suggest not using a Number datatype for this field at all; instead, use a
Currency datatype. A Currency field is a huge scaled integer which stores
exactly four decimal places, no more, no fewer; you can hide some of those
four digits by setting the Format or decimal places properties, but they're
still there.

Or, you can use

=Round([Trailer 1 Gross]-[Trailer 1 Tare], 2)

to round the result to two decimal places. I'd really suggest a Currency
datatype though.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi there,

Just a bit confused at the moment & would love some help .

All other fields in this table are ok except this one.

I'm no access genius, I was reluctant to pay $15000 for a weighbridge
program so I made one myself.

The control source is as follows for the Trailer 1 Nett field,
=[Trailer 1 Gross]-[Trailer 1 Tare]
The numbers(& result) are,
41.08-34.28 = 6.800003 (should display as just 6.8)

It just seems to be ignoring all the decimal formats (set to 2) we have
place which are working perfectly everywhere else except for this 1 record.

Thanks in advance.

=Format([Trailer 1 Gross]-[Trailer 1 Tare],"#.00")

Will return 2 decimals.

.... as a text string, not as a number, however. Good idea for display, but may
cause trouble for further calculations!

John W. Vinson [MVP]

 

[Joshua J]

Thanks Guys. Good result. Happy Xmas

Hi there,

Just a bit confused at the moment & would love some help .

All other fields in this table are ok except this one.

I'm no access genius, I was reluctant to pay $15000 for a weighbridge
program so I made one myself.

The control source is as follows for the Trailer 1 Nett field,
=[Trailer 1 Gross]-[Trailer 1 Tare]
The numbers(& result) are,
41.08-34.28 = 6.800003 (should display as just 6.8)

It just seems to be ignoring all the decimal formats (set to 2) we have
place which are working perfectly everywhere else except for this 1 record.

Thanks in advance.

It appears that you're using a Single Float size number. Floating point
numbers, by their nature, are approximations - a single float is accurate to
about 7 decimal places. Just as the fraction 1/7 cannot be represented exactly
as a decimal number, so the fraction 1/100 (or 1/10) cannot be represented
exactly as a binary fraction - and therefore not as a floating point number.

I'd suggest not using a Number datatype for this field at all; instead, use a
Currency datatype. A Currency field is a huge scaled integer which stores
exactly four decimal places, no more, no fewer; you can hide some of those
four digits by setting the Format or decimal places properties, but they're
still there.

Or, you can use

=Round([Trailer 1 Gross]-[Trailer 1 Tare], 2)

to round the result to two decimal places. I'd really suggest a Currency
datatype though.

John W. Vinson [MVP]