Round a Value to Specified Number

[Tantan]

Dear Modest,

Imagine the following table with the it's data.

Quantity
1.56
2.09
0.99
0.33
4.87

I want the code to do the following precedures:
- Start with the 1stField (1.56). Count the int of 1.56 and replace the result with the field and keep the remain fraction into account and add it to the next field ==> int(1.56) = 1, remained fraction 0.56. The same job for the next fields with looping. Therefore, the code does like this:

Quantity
1 '(0.56) into account to be added to next field => 0.56+2.09=2.65
2 '(0.65) into account to be added to next field => 0.65+0.99=1.64
1 '(0.64) into account to be added to next field => 0.64+0.33=0.97
0 '(0.97) into account to be added to next field => 0.97+4.87=5.84
5 '(0.84) into account to be added to next field => etc.

The source code to be modified is below:

Dim iTotal As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDB
Set rs = db.OpenRecordset("table name",dbOpenDynaset)

rs.Movelast: rs.Movefirst
iTotal = 0
Do Until rs.EOF
With rs
If .Fields("Quantity") < 1 Then
If iTotal + .Fields("Quantity") < 1 Then
iTotal = iTotal + .Fields("Quantity")
.Edit
.Fields("Quantity") = 0
.Update
Else
iTotal = iTotal + .Fields("Quantity") - 1
.Edit
.Fields("Quantity") = 1
.Update
End If
End If
End With
rs.MoveNext
Loop

 

[AlanJ10]

Right, try this
Dim iTotal As Double
Dim dValue As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)

rs.MoveLast: rs.MoveFirst
iTotal = 0

With rs
If .Fields("Order") >= 1 Then
iTotal = .Fields("Order") - Int(.Fields("Order"))
Else
iTotal = .Fields("Order")
End If
End With

rs.MoveNext

Do Until rs.EOF
With rs
dValue = iTotal + .Fields("Order")

If dValue >= 1 Then
iTotal = dValue - Int(dValue)
Else
iTotal = dValue
End If

End With
rs.MoveNext
Loop

Sorry, I'm a bit lost with what the original code is doing at the end, do you want to write these values back to the table, or are you just looking to get a value at the end?

Edit - At the end of the Function iTotal will contain the float (0.84 in your example) and dValue will contain the whole value (5.84 in your example) - hope this helps

 

[Tantan]

Dear AlanJ10,

Thank you so much for your help. The added code at the end is the original one which only works for values less\more than 1. But I need the general rule which you have made and only the integer values should be placed in the table. Therefore, no fraction should be appeared!

Thanks again for your great help but your code doesn't make change to the table. Would you please look though it again and inform me if any changes is needed. (I also also added .Edit and .Update into code; maybe you can define the right place for these!)

 

[AlanJ10]

Ok, so if I'm understanding you correctly, you want to place the integer values in the table, so using your example:

1.56 - should this be replaced by 1, or should it be replaced by 2 (integer value of 0.56+2.09)?
2.09
0.99
0.33
4.87

Dim iTotal As Double
Dim dValue As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)

rs.MoveLast: rs.MoveFirst
iTotal = 0

With rs
If .Fields("Order") >= 1 Then
iTotal = .Fields("Order") - Int(.Fields("Order"))
.Edit
.Fields("Order") = Int(.Fields("Order"))
.Update
Else
iTotal = .Fields("Order")
.Edit
.Fields("Order") = 0
.Update
End If
End With

rs.MoveNext

Do Until rs.EOF
With rs
dValue = iTotal + .Fields("Order")

If dValue >= 1 Then
iTotal = dValue - Int(dValue)
.Edit
.Fields("Order") = Int(dValue)
.Update
Else
iTotal = dValue
.Edit
.Fields("Order") = 0
.Update
End If

End With
rs.MoveNext
Loop

This code will insert the values 1, 2, 1, 0, 5 into your table, which I think is what you want.
If I've misunderstood then let me know what values should be written into the table and where and I'll try to tidy it up a bit.

 

[Tantan]

Dear Alan,

Well done!....Thank you so much. It works!

 

[modest]

Sorry, I just got your message.

The original code was under the assumption that you were only dealing with fractions. For instance, the original post (now deleted) was misleading in how data was stored in the table.

I think you stated that there were no whole numbers and that the data would look like:
.12
.56
.73
.22

Glad to see you got what you wanted though.

Cheers,
Modest