calculate results show funny figures

[KarenY]

Hi,

I have to run a qry to get the different value of 2 fields from 2 tables.
Yet some rows come back with funny values for all those minus figures.
(e.g. -7.27595761418343E-12) when 2 tables have the same value.

I have 2 tables with all the invoices, both tables' data are downloaded from
our ODBC-tables from our server except the "SumNetTotal" which is the sum of
all the other fields in the table for all the invoices in tbl_NetTotals.

tbl_INVCtotals has a field "invcSum" for all the invoices.

Both tables have the same invoices.

My field : "diffValue" = (Nz([invcSum]))-(Nz([SumNetTotal]))

Those "funny minus values" are actually having the same amount in both
tables, should be zero.
I can understand that's probably the SumNetTotal is from the sum of all the
other fields which may probably have different decimals for each value.

Would anybody please help how do I round up the value (I did try to use
ROUND and RND but didn't work out).
I know I can run an update qry afterwards but I thought there may probably
be a function for me to input when I run the "diffValue" ?

I am using MS Access 2003.

Appreciate you help.
thanks
Karen

 

[Tom van Stiphout]

On Sun, 4 Oct 2009 14:46:01 -0700, KarenY

You must be using a funny data type like Double rather than Currency.
Not a good idea if in fact these are currency. With Double, very small
discrepancies are to be expected. As you may know -7E-12 is
-0.000000000007. If you have to stick with Double or Single, you
should round the value, for example:
diffValue = Round(your_expression, 4)

-Tom.
Microsoft Access MVP

 

[Allen Browne]

As you guessed, the nnnE-12 represents a very small number, where the
decimal place is moved 12 places to the left (so lots of zeros after the
decimal point.)

You could lose the small number of decimal places in several ways, e.g.
a) Convert the value to currency:
CCur(Nz([invcSum],0) - Nz([SumNetTotal],0))
Currency handles no more than 4 decimal places, and typically displays 2.

b) Round to a specified number of places, e.g.:
Round(Nz([invcSum],0) - Nz([SumNetTotal],0), 2)

Or, you could use the properties to suppress the display of those places.
Set Format to Fixed, and Decimal Places to 2 or 0.

 

[KarenY]

thank you for both of you, Allen and Tom.

Both ways work perfect !!! Make my work a lot easier as well as learned
something !

As you guessed, the nnnE-12 represents a very small number, where the
decimal place is moved 12 places to the left (so lots of zeros after the
decimal point.)

You could lose the small number of decimal places in several ways, e.g.
a) Convert the value to currency:
CCur(Nz([invcSum],0) - Nz([SumNetTotal],0))
Currency handles no more than 4 decimal places, and typically displays 2.

b) Round to a specified number of places, e.g.:
Round(Nz([invcSum],0) - Nz([SumNetTotal],0), 2)

Or, you could use the properties to suppress the display of those places.
Set Format to Fixed, and Decimal Places to 2 or 0.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have to run a qry to get the different value of 2 fields from 2 tables.
Yet some rows come back with funny values for all those minus figures.
(e.g. -7.27595761418343E-12) when 2 tables have the same value.

I have 2 tables with all the invoices, both tables' data are downloaded
from
our ODBC-tables from our server except the "SumNetTotal" which is the sum
of
all the other fields in the table for all the invoices in tbl_NetTotals.

tbl_INVCtotals has a field "invcSum" for all the invoices.

Both tables have the same invoices.

My field : "diffValue" = (Nz([invcSum]))-(Nz([SumNetTotal]))

Those "funny minus values" are actually having the same amount in both
tables, should be zero.
I can understand that's probably the SumNetTotal is from the sum of all
the
other fields which may probably have different decimals for each value.

Would anybody please help how do I round up the value (I did try to use
ROUND and RND but didn't work out).
I know I can run an update qry afterwards but I thought there may probably
be a function for me to input when I run the "diffValue" ?

I am using MS Access 2003.

Appreciate you help.
thanks
Karen