Expression Results in Truncated Fraction

G

Guest

I have created an expression joining 5 tables to give me results so that I
can find a variance. In some cases, the table will have a null value and I
have accounted for that with the Nz expression. An example of my formula
appears below.
Expr1:
Nz([GROSS_PAY],0)-Nz([TAX_WAGES],0)-Nz([WAGE_AMOUNT],0)-Nz([401K-Defered
Comp.DED_AMT],0)-Nz([Section 125.DED_AMT],0)
However a large number of my results end up with 2.82E-06 or other simular
numbers which appear to be a truncated fraction. My tables only contain fixed
numbers with 2 decimal places. Does anyone know how to correct this problem?
 
F

fredg

I have created an expression joining 5 tables to give me results so that I
can find a variance. In some cases, the table will have a null value and I
have accounted for that with the Nz expression. An example of my formula
appears below.
Expr1:
Nz([GROSS_PAY],0)-Nz([TAX_WAGES],0)-Nz([WAGE_AMOUNT],0)-Nz([401K-Defered
Comp.DED_AMT],0)-Nz([Section 125.DED_AMT],0)
However a large number of my results end up with 2.82E-06 or other simular
numbers which appear to be a truncated fraction. My tables only contain fixed
numbers with 2 decimal places. Does anyone know how to correct this problem?

The values are being shown in scientific format.
You'll have to check the Format property of the column.

It should not be set to Scientific.
If that isn't it, wrap the entire expression within a Format()
expression.
Exp1:Format(Nz(]Gross_Pay],0) - Nz(... etc....,"#,###.00")
 
J

John Spencer (MVP)

I would either use a currency field for all these money fields or use the CCur
conversion function in your formula. That should take care of any problems with
single or double fields. These number types (floating point numbers) are
inherently unable to totally accurately handle base 10 numbers with decimal portions.

CCUR(Nz([GROSS_PAY],0))
-CCUR(Nz([TAX_WAGES],0))
-CCUR(Nz([WAGE_AMOUNT],0))
-CCUR(Nz([401K-Defered Comp.DED_AMT],0))
-CCUR(Nz([Section 125.DED_AMT],0))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top