convert long decimal number to 2 decimal places

[jammyp]

hi,
I have dividing one int field by another and the results varies from 0 to 9999.999999999999

When I add a SUM(newnumber) into a group field in reports I get the error :
This expression is typed incorrectly, or it is too complex to be evaluated.

I think it might have something to do with the large decimal place.

from my query can I convert this number to 9999.99 at most ?
ie, only 2 decimal places..
Access should then beable to add the values,, correct ?


thank you for any help..

 

[jammyp]

Hi, After further investigation the problem with this is I have values like 0/0
and 1/0 how can I change my division row to work with those sorts of values ?

 

[gemma-the-husky]

is this in a query?

1. you could try integer division

not a/b, but a\b - not sure if it rounds or truncates off hand, but the answer is an integer

if you need 2dps then round(number,2) will do that

2. you need to test for zeros/blanks first first
you need something like

iif(trickyfield=0,0,normalfield/trickyfield) - EXCEPT this STILL wont help because all terms in an iif are evaluated - so you will probably have to do it in a function.

 

[jammyp]

thanks Gemma, a simple IIF statement is all I needed !

 

[Dairy Farmer]

Field3 = Field1/Field2
If field1 = 0 then answer must be 0
If field2 = 0 then answer must be 0
If both fields <> 0 the field3 = field1/field2

=[COLOR="royalblue"]iif(field1=0,0[/COLOR],[COLOR="seagreen"]iif(field2=0,0[/COLOR],[COLOR="orange"]field1/field2[/COLOR]))

 

[vbaInet]

Field3 = Field1/Field2
If field1 = 0 then answer must be 0
If field2 = 0 then answer must be 0
If both fields <> 0 the field3 = field1/field2

=[COLOR=royalblue]iif(field1=0,0[/COLOR],[COLOR=seagreen]iif(field2=0,0[/COLOR],[COLOR=orange]field1/field2[/COLOR]))

This thread is over 4 years old. I don't see why you needed to revive it.

What gemma-the-husky gave was sufficient to solve the problem. It is the divisor that matters so the extra check you are doing is unecessary.