I have a VB function which returns a single. The routine is called from a calculated column in a query and the returned value needs to be rounded to two decimal places.
I put a Round function in the VB routine, but it didn't do anything i.e. the returned value in the query had 15, 16 etc decimal places (obviously depending on the fraction).
I then removed the Round in the function and put it on the calculated column. This hasn't done anything, either.
What am I doing wrong and how can I get these return values rounded to 2 decimal places, please?
Thanks
Silverback
Rounding not happening
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Rounding not happening
In the first place, I'd make the return value of the function a Double. The Single data type is rather inaccurate, so you often get rounding errors.
In the second place, I'd specify a format for the column in the query with 2 decimal places - either Fixed or, if applicable, Currency.
In the second place, I'd specify a format for the column in the query with 2 decimal places - either Fixed or, if applicable, Currency.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 780
- Joined: 29 Jan 2010, 13:30
Re: Rounding not happening
Thanks - all working now.
Learnt yet more from this posting - did not know that a query column could be formatted, so thanks for that, too.
Silverback
Learnt yet more from this posting - did not know that a query column could be formatted, so thanks for that, too.
Silverback
-
- 5StarLounger
- Posts: 780
- Joined: 29 Jan 2010, 13:30
Re: Rounding not happening
Hans
Forgot to ask - purely for curiosity - if you know why the rounding did not work.
Thanks
Silverback
Forgot to ask - purely for curiosity - if you know why the rounding did not work.
Thanks
Silverback
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Rounding not happening
If the numbers were similar to 4.569999 or 6.780001, it was probably a matter of rounding errors. Computers work with binary numbers, and many (most) decimal fractions can't be represented exactly in binary, so rounding errors are unavoidable.
Otherwise, it's hard to say without seeing the database.
Otherwise, it's hard to say without seeing the database.
Best wishes,
Hans
Hans