Results 1 to 10 of 10
Thread: VBA Module  Calculation Problem

20100106, 06:27 #1
 Join Date
 Oct 2009
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
Sub AddTest()
AddData "SubProcesses", 10
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

20100106, 07:18 #2
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
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
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
Andrew

20100106, 07:35 #3
 Join Date
 Aug 2001
 Location
 Evergreen, CO, USA
 Posts
 6,641
 Thanks
 3
 Thanked 65 Times in 64 Posts
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?
Wendell

20100106, 07:57 #4
 Join Date
 Oct 2009
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20100106, 08:50 #5

20100106, 09:02 #6
 Join Date
 Oct 2009
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20100106, 09:07 #7
 Join Date
 Oct 2009
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20100106, 09:12 #8

20100106, 09:32 #9
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
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
Then Just reference it in your routine
eg
Code:rstOut!Total = RoundUp(rstOut!Total,0)
Or you could just use the Technique in your routine
e.g.
rstOut!Total = IIF(rstOut!TotalInt(rstOut!Total)=0,rstOut!Total,Round(rstOut!Tota l+0.5,0))Andrew

20100106, 09:42 #10
 Join Date
 Oct 2009
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excellent  thanks so much Andrew!
Best.