Roudup Function in a query

  • Thread starter Thread starter JK
  • Start date Start date
J

JK

In MS Excel you have a Roundup function and in Access there is only a Round
function. Is there any method I can use to have the same function in Access.

My calculation is to take a amount in one field and calculate the tax on
that field and add it into a new field. e.g Tax_Inc: [Amount].*1.14

When I do this calculation I sometimes get a amount of e.g 0.855 and now
want this to become 0.86.

How is this possible in Access

JK
 
Last Time I asked, it was in microsoft.public.access.forms . I would now like to know if it's possible in a query?

TIA
 
Hi,


The same formula should be applied in a query. Here, for Access 2000 or later,

Tax: Int( 0.5+ taxPercent * Amount * CDec(100.0)) / CDec(100.0)



Hoping it may help,
Vanderghast, Access MVP


Last Time I asked, it was in microsoft.public.access.forms . I would now like to know if it's
possible in a query?

TIA
 
Hi,


And the dot is the decimal separator (by opposition to be the separator for items in a list) ?


Do you get the same error if you try, in the Immediate Debug Window:


? Int( 0.5+ 0.07 * 12.34 * CDec(100.0) ) / CDec(100.0)


Vanderghast, Access MVP
 
I needed Excel's Roundup() function in Access, where 0.851 stays 0.85 and
1.702 stays 1.70. But then again the format "Decimal(6,2)" could also do the
job, couldn't it?

Thank you for your help.

JK


Allen Browne said:
JK, your initial message stated you wanted a RoundUp() function like
Excel's, not a Round() function like Access has.

Open Excel. Enter:
=ROUNDUP(0.851,2)
and in another cell:
=ROUNDUP(1.702,2)
Is this the result you want, or do you want the Round() function after all?

(There is a slight difference in the function I posted compared to Excel's.
Excel's RoundUp() rounds away from zero, where the one I posted rounds
upwards.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")
JK said:
As you said the problem is that 0.851 changes to 0.86 and 1.702 changes to
1.71 which in effect is wrong.

Thank you for your help

JK

Allen Browne said:
The same process applies.

1. Select the Module tab of the Database window.
2. Click New.
3. Paste the function below into the new module.
4. Save the module with a name such as Module1.
5. Check that it compiles (Compile on Debug menu).

You can now use the function in your query. For example:
Tax_Inc: RoundUp([Amount] * 1.14, 2)

Regarding you example, this function will round all values upwards, e.g.
0.851 rounds up to 0.86.

Function RoundUp(varValue, Optional iPlaces As Integer = 2)
If IsNumeric(varValue) And Not IsError(varValue) Then
RoundUp = CDec(-Int(-varValue * 10 ^ iPlaces)) / 10 ^ iPlaces
Else
RoundUp = Null
End If
End Function
Click to expand...
Click to expand...
Click to expand...
 
JK, please tell me the exact expression you used in Excel with RoundUp(),
and what version of Excel gave you this result.
 
Back
Top