Access not multiplying simple equation correctly

Question

Hi,

I've made a very simple expression that multiplies 3 fields (length, width, height) to create a volume.  I've tried rounding, and that doesn't correct the problem. I've tried the following:

volume: [length]*[width]*[heigth]

volume: ([length]*[width]*[height])

volume: ROUND([length]*[width]*[height], 1)

volume:ROUND(([length])*([width])*([height]), 1)

All of these give me the same problematic results.

The numbers were imported to the database with only one decimal, and for example, in Access 3.5*2.9*0.9 = 8.9 or 8.89347….

It's supposed to equal 9.135, or 9.1.

Can anyone please explain what the problem might be?

Thank you!!

Answer 1

Hi all,

I figured out what was going on - I rounded the values to one decimal in Excel and Access apparently picked up the original formatting.  The resulting multiplication values in Access are based on the original non-rounded values.  Fine, more precise.  I just wish that hadn't suck 5 hrs of my time.  Now I know.

Thanks for your responses.

Kate

Answer 2

Well, thank you for trying.

Some of the rows are correct - I'm guessing because the numbers just work out that way (e.g., 3.4*1.5*0.3=1.5).  And when I first checked the results I thought that it worked fine, but then I laid eyes on the row that was supposed to equal 9.1.

Perhaps I should just pretend I never noticed it…but I can't!  It needs to be correct.

If anyone else has any ideas as to why this is not multiplying correctly, I'd really appreciate the feedback.

Thanks in advance!

Answer 3

Not too sure what to say.

I just tested with the value you posted and I get

9.135 and 4.59 if I don't use the Round()

9.1 and 4.6 if I use Round(xxxxxx,1)

Do you have Service Pack 2 installed?

Answer 4

Hi Daniel,

I'm not quite sure how to attach a sample database, but I'm just simply multiplying 3 fields which I've shown in my question - they are named length, width, height.

The example I gave is one row from the query where I've tried to make this calculation, and if I rounded to a whole number I'd get 9 but that doesn't give us enough precision as we're dealing with cubic meters - we'd like to go out to one or two decimals.

Using the equation you gave above, but simply substituting the field name in with brackets for the numbers, I get 8.89, which is incorrect.

I've used older versions of Access before, and used similar equations written the same exact way without any problems. The only difference is that then I had long numbers, and this time - to avoid rounding errors like possibly this one (?!) - I've imported a table with values out to only one decimal.

I have read how to round (again and again) - although the help menu isn't much help (it doesn't return anything useful when I type in "round", but I've been all over the web and back, and it's seems that I'm using the function correctly:

round([field1]*[field2]*[field3], 1{or2})…only problem is that it gives me the wrong number.

To give you an idea of what this looks like:

length_m     width_m     height_m     volume_m3

3.4               2.7               0.5              4.5 {should be 4.6}

3.5               2.9               0.9              8.9 {should be 9.1}

It really is just that simple, but it's giving me incorrect results.

Any ideas as to why?

Thanks!

Answer 5

First off, have you read the Access help file on the Round().  It does not work the way you'd expect it to, at least not the way I ever expected it to.

Secondly,

Round(3.5*2.9*0.9,1) = 9.1

I quickly created a test table, entered your value and built a query with an simple expression

volume: [length]*[width]*[heigth] and it gave me 9.135

unless you can give us more example or perhaps post a sample db to review it is very hard to diagnose the issue.

What are the filed data types in your table?

Also, please be aware you are using Reserved Words, which you should avoid as they can give odd results!