473,241 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

Problem rounding to the nearest 0.5

Hi everyone,

I have a code that I can use in Excel to round to the nearest 0.5, but I cannot get it to work in Access. When I try to execute it I get the following error message: "Compile error: user defined type not defined." Any idea what I need to do to change my code to work in Access?

Here's the code:
Expand|Select|Wrap|Line Numbers
  1. 'Round to nearest 0.5
  2. Public Function vbaRoundTO(dblValue As Double, dblRoundTo As Double, _
  3.     Optional RoundingOption As rOpt = rNearest) As Double
  4.     Dim dblRoundedMutliple As Double
  5.     Dim dblValueDiv As Double
  6.     Dim dblValueNew As Double
  7.  
  8.      'Set default retrun value if dblRoundTo = 0
  9.     If dblRoundTo = 0 Then
  10.         vbaRoundTO = 0 'OR vbaRoundTO = dblValue
  11.         Exit Function
  12.     End If
  13.  
  14.      'Find multiple of RoundToSmallest
  15.     dblValueDiv = dblValue / dblRoundTo
  16.  
  17.      'Option to RoundUP or RoundDOWN
  18.     Select Case RoundingOption
  19.     Case rNearest
  20.          'Round multiple to nearest
  21.          'DO NOT USE VBA Round() function.
  22.          'VBA : Round(2.5,0) = 2, i.e. rounds >=0.5 to 0 not 1
  23.         dblRoundedMutliple = vbaRound(dblValueDiv, 0)
  24.     Case rUp
  25.          'Round multiple UP
  26.         dblRoundedMutliple = vbaRound(dblValueDiv, 0, 1)
  27.     Case rDown
  28.          'Round multiple DOWN
  29.         dblRoundedMutliple = vbaRound(dblValueDiv, 0, 2)
  30.     Case Else
  31.     End Select
  32.  
  33.      'Calculate new "rounded-to" value
  34.     dblValueNew = dblRoundedMutliple * dblRoundTo
  35.  
  36.      'Return value
  37.     vbaRoundTO = dblValueNew
  38.  
  39. End Function
  40.  
Thank you for helping!

Linda
Aug 23 '07 #1
1 5305
I figured out the problem. I had failed to copy over all the necessary code. In case anyone needs it. Here it is.

Code:
Expand|Select|Wrap|Line Numbers
  1. 'Set variables
  2. Public Enum rOpt
  3.     rNearest
  4.     rUp
  5.     rDown
  6. End Enum
  7. 'Rounding function
  8. Public Function vbaRound(dblValue As Double, intDecimals As Integer, _
  9.     Optional RoundingOption As rOpt = rNearest) As Double
  10.     Dim dblPlacesFactor As Double
  11.     Dim dlbRoundFactor As Double
  12.  
  13.     If intDecimals < 0 Then
  14.         vbaRound = 0
  15.         Exit Function
  16.     End If
  17.  
  18.     dblPlacesFactor = 10 ^ intDecimals
  19.  
  20.     Select Case RoundingOption
  21.     Case rNearest 'Round to Nearest
  22.         dlbRoundFactor = 0.5
  23.     Case rUp 'Round UP
  24.         dlbRoundFactor = 1
  25.     Case rDown 'Round DOWN
  26.         dlbRoundFactor = 0
  27.     End Select
  28.  
  29.     vbaRound = Int(dblValue * dblPlacesFactor + dlbRoundFactor) / dblPlacesFactor
  30. End Function
  31. 'Round to nearest 0.5
  32. Public Function vbaRoundTO(dblValue As Double, dblRoundTo As Double, _
  33.     Optional RoundingOption As rOpt = rNearest) As Double
  34.     Dim dblRoundedMutliple As Double
  35.     Dim dblValueDiv As Double
  36.     Dim dblValueNew As Double
  37.  
  38.      'Set default retrun value if dblRoundTo = 0
  39.     If dblRoundTo = 0 Then
  40.         vbaRoundTO = 0 'OR vbaRoundTO = dblValue
  41.         Exit Function
  42.     End If
  43.  
  44.      'Find multiple of RoundToSmallest
  45.     dblValueDiv = dblValue / dblRoundTo
  46.  
  47.      'Option to RoundUP or RoundDOWN
  48.     Select Case RoundingOption
  49.     Case rNearest
  50.          'Round multiple to nearest
  51.          'DO NOT USE VBA Round() function.
  52.          'VBA : Round(2.5,0) = 2, i.e. rounds >=0.5 to 0 not 1
  53.         dblRoundedMutliple = vbaRound(dblValueDiv, 0)
  54.     Case rUp
  55.          'Round multiple UP
  56.         dblRoundedMutliple = vbaRound(dblValueDiv, 0, 1)
  57.     Case rDown
  58.          'Round multiple DOWN
  59.         dblRoundedMutliple = vbaRound(dblValueDiv, 0, 2)
  60.     Case Else
  61.     End Select
  62.  
  63.      'Calculate new "rounded-to" value
  64.     dblValueNew = dblRoundedMutliple * dblRoundTo
  65.  
  66.      'Return value
  67.     vbaRoundTO = dblValueNew
  68.  
  69. End Function
Aug 23 '07 #2

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

Similar topics

3
by: b | last post by:
Hello all, we have a table that is part of our accounting package that stores decimals as such: 123.45678. However the reporting standards with the accounting system display that as 123.45 note...
14
by: calan | last post by:
Does anyone have a function that will round a number to 0 or .5? I have a form where I'm entering a number in inches. I need to round it to the nearest 1/2 inch (onChange). The split will be...
8
by: Zorpiedoman | last post by:
Howcome: Dim D as decimal = .5D msgbox d.Round(D, 0) this returns "0" Now when I went to school .5 rounds UP to 1 not DOWN to zero?????!!! Documentation says this, but what the heck are...
6
by: Jeff Boes | last post by:
(asked last week on .questions, no response) Can anyone explain why this happens? (under 7.4.1) select '2004-05-27 09:00:00.500001-04' :: timestamp(0) ; timestamp ---------------------...
12
by: 6tc1 | last post by:
Hi all, I just discovered a rounding error that occurs in C#. I'm sure this is an old issue, but it is new to me and resulted in a fair amount of time trying to track down the issue. Basically...
8
by: John | last post by:
Hi I am calculating tax as follows; = * / 100# Client complains that there is sometimes difference in pennies in tax calculation. Is there a better (more precise) way to calculate tax? ...
5
by: Spoon | last post by:
Hello everyone, I don't understand how the lrint() function works. long lrint(double x); The function returns the nearest long integer to x, consistent with the current rounding mode. It...
206
by: md | last post by:
Hi Does any body know, how to round a double value with a specific number of digits after the decimal points? A function like this: RoundMyDouble (double &value, short numberOfPrecisions) ...
20
by: jacob navia | last post by:
Hi "How can I round a number to x decimal places" ? This question keeps appearing. I would propose the following solution #include <float.h> #include <math.h>
30
by: bdsatish | last post by:
The built-in function round( ) will always "round up", that is 1.5 is rounded to 2.0 and 2.5 is rounded to 3.0. If I want to round to the nearest even, that is my_round(1.5) = 2 # As...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.