Help with math

[ibmerlin]

I am having some problems with some calculations in a query. I want to enter a number and have an Expression divide that number by 254 and then multiply the total by another feild.
TotalWT: ([GramWT]/254)*[numberofbatches]
If I were to enter 100 I should get .3937007 and if the numberofbatches is 10 then the total should be 3.9. The problem is that it seems to be rounding down the number after the division to 0 and then multiplying that by 10.
What do I need to do to get this to work?
Thanks
Merlin

 

[Jon K]

On my system, I was able to obtain 3.9 using the Round() function when the number of batches is 10.

numberofbatches		TotalWT			RoundedTotalWT
1			0.393700787401575	0.4
2			0.78740157480315	0.8
3			1.18110236220472	1.2
4			1.5748031496063		1.6
5			1.96850393700787	2
6			2.36220472440945	2.4
7			2.75590551181102	2.8
8			3.1496062992126	3.1
9			3.54330708661417	3.5
[b]10			3.93700787401575	3.9[/b]
11			4.33070866141732	4.3

You can try the attached database.

 

[KKilfoil]

You may have a problem with your data types.

If GramWT is an integer field (i.e. long), then you are dividing an integer by another integer (254). In Access, the result will be assumed to be an integer, with whatever rounding is required done to the result.

One way to avoid this is to change the type of GramWT to be single or double, which will then participate in calculations without roundoff.

Another workaround would be to divide by 254.0 or 254# to force the result to be a floating-point value.

 

[Jon K]

If GramWT is an integer field (i.e. long), then you are dividing an integer by another integer (254). In Access, the result will be assumed to be an integer

I wonder if Integer/Integer=Integer is version specific.

On my system, I can only get Integer/Integer=Double Precision, no matter if Integer is an integer or long.


In my original example, [GramWT] was a parameter and numberofbatches was an integer field (long). [GramWT]/254*[numberofbatches] came out as double precision numbers as shown in the query.

When I added an integer field [GramWT] (long) in the table, [GramWT]/254*[numberofbatches] still came out as double precision numbers in the query.

I then changed the field [GramWT] from long to integer and I still got double precision.

 

[ibmerlin]

Thanks everyone

Thanks everyone that helped. Its working fine now.