# Thread: VBA Module - Calculation Problem

1. I have the following bit of code,which works just fine, however the last update returns a value rounded to the nearest number down and I want it rounded to the nearest number up - is there a way I can do this? For example, if the inital value is 85, its returning a value of 42 when I want 43. Thanks for your help.

End Sub

'ADDS TOTALS FROM SubProcesses TABLE TO LINE 10 OF TBLA
Sub AddData(strSource As String, lngLineID As Long)
Dim dbs As DAO.Database
Dim rstIn As DAO.Recordset
Dim rstOut As DAO.Recordset
Dim i As Long
Set dbs = CurrentDb
Set rstIn = dbs.OpenRecordset(strSource, dbOpenForwardOnly)
Set rstOut = dbs.OpenRecordset("SELECT * FROM TblA WHERE LineID=" & _
lngLineID, dbOpenDynaset)
' Optional: set all fields to 0 except the first two

rstOut.Edit
For i = 3 To rstOut.Fields.Count - 1
rstOut.Fields(i) = 0
Next i
rstOut.Update

' Loop through data records
Do While Not rstIn.EOF

' Update appropriate field and total
rstOut.Edit
rstOut.Fields(rstIn!BU) = rstOut.Fields(rstIn!BU) + 1
rstOut!Total = rstOut!Total + 1
rstOut.Update

rstIn.MoveNext
Loop

'Divide totals by half
rstOut.Edit
rstOut!Total = (rstOut!Total) * 0.5
rstOut!GCC = (rstOut!GCC) * 0.5
rstOut!PBS = (rstOut!PBS) * 0.5
rstOut!CR = (rstOut!CR) * 0.5
rstOut!BOPS = (rstOut!BOPS) * 0.5
rstOut!GOFIN = (rstOut!GOFIN) * 0.5
rstOut.Update

ExitHandler:
On Error Resume Next
rstOut.Close
Set rstOut = Nothing
rstIn.Close
Set rstIn = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

2. Access Unlike Excel only has a Round Function, NOT a RoundDown or RoundUp function
so I think you are going to have to use a Custom Function in a Module and then call it from your routine

I have put one below
I haven't bothered to error trap it for NULL inputs but you could
It also rounds to nearest 10, 100 etc by using - places

Code:
```Function RoundDown(dblVal As Double, intDP As Integer) As Double

Dim lngVal As Long, dblRound As Double

lngVal = Int(dblVal * 10 ^ intDP)

dblRound = lngVal / (10 ^ intDP)

RoundDown = dblRound

End Function```
You would then have to call this as part of the calculation in your code
No idea what accuracy you are after, but for example

Code:
`rstOut!Total = RoundDown((rstOut!Total) * 0.5,1)`

Would round Down the answer to 1 DP

3. Just to clarify - if you have a even number and the result is exactly half of the original value, do you want to round that up, or leave it at exactly half? Also are you using integer or floating point field types?

4. Hi both, thanks for your responses - yes I am using Long Integer and I want to round it up. I have got a fairly simple work around by multiplying by 0.5001 now which gives the right results, but I'm worried its not technically correct/best. For these tables its unlikely to return a value of greater than a couple of thousand so I didn't think it would be too important, but I'd prefer to know the right way to do it for the future.

Best.

5. Originally Posted by DazedandConfused
Hi both, thanks for your responses - yes I am using Long Integer and I want to round it up. I have got a fairly simple work around by multiplying by 0.5001 now which gives the right results, but I'm worried its not technically correct/best. For these tables its unlikely to return a value of greater than a couple of thousand so I didn't think it would be too important, but I'd prefer to know the right way to do it for the future.

Best.
Does not the RoundDown function used for example as below do the same job

rstOut!Total = RoundDown(rstOut!Total,0)

6. Hi as a beginner not sure of these functions - will have a check to see if there's a roundup function, which is the one I'd be needing. Thanks for your help.

7. I am using Access 2003 and cannot find a RoundDown function (or a RoundUp function which is what I'd be needing) in the help files - is it only relevant to later versions? Best

8. Originally Posted by DazedandConfused
Hi as a beginner not sure of these functions - will have a check to see if there's a roundup function, which is the one I'd be needing. Thanks for your help.
OOPS, sorry, misread the original question and thought you were after a RoundDown function

Watch this space!

9. IF all you want to do is to RoundUp to the Nearest Whole Number then you could use this function

Code:
```
Function RoundUp(dblVal As Double) As Long

Dim dblRound As Double

If (dblVal - Int(dblVal)) <> 0 Then
dblRound = Round(dblVal + 0.5,0)
Else
dblRound = dblVal
End If

RoundUp = dblRound

End Function```
Create a Module and paste the function into it

Then Just reference it in your routine

eg

Code:
`rstOut!Total = RoundUp(rstOut!Total,0)`
(Obviously I meant RoundUp not RoundDown which I had written)

Or you could just use the Technique in your routine

e.g.

rstOut!Total = IIF(rstOut!Total-Int(rstOut!Total)=0,rstOut!Total,Round(rstOut!Tota l+0.5,0))

10. Excellent - thanks so much Andrew!

Best.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•