By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,261 Members | 1,684 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,261 IT Pros & Developers. It's quick & easy.

Multiplying in Access

P: 12
I am trying to multiply 2 fields in Access
Field1 has a value of 70.1
Field2 has a value of 2.05

Formula is
[Field1] * [Field2]

Result is 143.704996657372

Help!! I really need it to give me the correct result of 143.705
I don't have a clue why access is not multiplying this correctly.

Just in case this makes a difference:
Field1 is defined on the table as Currency-Currency-Auto (decimal places)
Field2 is defined on the table as Number-Single-Fixed (6 decimal places)
Mar 8 '10 #1

✓ answered by ADezii

Expand|Select|Wrap|Line Numbers
  1. =FormatNumber([Field1] * [Field2],3)

Share this Question
Share on Google+
4 Replies

Expert 5K+
P: 8,698
Expand|Select|Wrap|Line Numbers
  1. =FormatNumber([Field1] * [Field2],3)
Mar 8 '10 #2

Expert Mod 2.5K+
P: 2,545
All floating-point calculations are subject to rounding errors. Your second field is defined as of type single-precision - the lowest-accuracy form of floating-point storage used in VBA. Even if you used double-precision floating point values - which I would use as a matter of routine and not use the Single type at all - you will still face rounding errors in calculations.

ADezii's suggestion of using formatting to round the number is a good one.

I would use a double-precision field and simply round it for display purposes, using either formatting to three decimal places as ADezii suggests or explicit rounding (assuming that no further calculations are necessary):

=Round([Field1]*[Field2), 3)

By the way, using names such as Field1 and Field2 is a waste of a field name. Give your fields meaningful names that tell the reader what your field is storing.

Mar 8 '10 #3

Expert Mod 100+
P: 2,321
Thats Access 2007 for you. Makes it way to easy to create tables with meaningless field names. :)
Mar 8 '10 #4

P: 12
Actually I do have better field names but for the purpose of posting the question I tried to simplify. The answer given by ADezii seems to have fixed by problem. Thank you everyone for your help.
Mar 9 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.