How to set decimal places (1 Viewer)

davebenwell · Feb 21, 2019

Hi,

I want to round the numbers up to £0.00 so if a value is 0.05426 round up to 0.06

Below is my sql not sure how to do it.

SELECT [Retail Union].[Call Band], [Retail Union].Description, [Retail Union].[Peak (ppm)], [Retail Union].[Off-Peak (ppm)], [Retail Union].[Weekend (ppm)], [Retail Union].[Minimum Duration (secs)], [Retail Union].[Billing Increment] AS [Billing Increment (secs)], [Retail Union].[Minimum Charge (p)], [Retail Union].[Maximum Charge (p)], [Retail Union].[Call Setup Charge (p)], [Retail Union].[Use Markup (Y/N)], [Retail Union].[Markup %], [Retail Union].[Cap at (s)], [Retail Union].[Cap Value (p)], [Retail Union].[Cap Which Calls (A/O/W)]
FROM [Retail Union];

JHB · Feb 21, 2019

Can't you use the round function?
https://www.techonthenet.com/access/functions/numeric/round.php

davebenwell · Feb 21, 2019

maybe but not sure how to wrap that around my SQL

Gasman · Feb 21, 2019

Along the lines of

Code:

ROUND([Retail Union].[Call Setup Charge (p)],2)

davebenwell · Feb 21, 2019

Thanks that works

isladogs · Feb 21, 2019

You can't use ROUND as it rounds downwards e.g Round(0.05426,2)=0.05

Various methods exist to solve thiis
See https://stackoverflow.com/questions/31980106/q-how-to-roundup-a-number-in-access-2013

The easiest (though not the most robust) is probably to add just a small fixed value to your number depending on the number of d.p. required
E.g if you want 2d.p. add 0.0049, for 3 dp add 0.00049 etc where number of zeroes after the decimal point =number of d.p. Required

For example,

Code:

=Round([NumberField]+0.0049,2)

Gasman · Feb 21, 2019

Oops, sorry Dave,
I thought you wanted to go up every time, but with JHB's question, I thought that was my error. Should have read the question closer.

davebenwell · Feb 21, 2019

Rounding is fine, even with the round downs it's not too bad.

How to set decimal places (1 Viewer)

davebenwell

New member

JHB

Have been here a while

davebenwell

New member

Gasman

Enthusiastic Amateur

davebenwell

New member

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

davebenwell

New member

Similar threads

Users who are viewing this thread