UPDATE Statement and Rounding

X

xRoachx

The following sub updates values in a table and is functioning correctly
except for one small problem. The dblOverallScore is rounding up when the
field is updated in the table. The VB variable is a double and the field
type is a number (percent) that allows for 2 decimal places. For example, if
dblOverallScore is .97346545, the field in the table is updated to 100.00%
instead of 97.35%. Any ideas on why the rounding is occuring and how I can
correct this? Thx in advance!

Private Sub Form_AfterUpdate()

Dim lngEligiblePts As Long
Dim lngEarnedPts As Long
Dim dblOverallScore As Double
Dim lngVisID As Long
Dim lngQaID As Long
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

lngEligiblePts = txtEligiblePts.Value
lngEarnedPts = txtEarnedPts.Value
dblOverallScore = Round(txtOverallScore.Value, 4)
strVisID = VisID.Value
strQaID = QAID.Value


strSQL = "UPDATE QA_PROV_VISITS_T " & _
"SET [EligiblePts] = " & lngEligiblePts & "," & _
"[EarnedPts] = " & lngEarnedPts & "," & _
"[OverallScore] = " & dblOverallScore & "" & _
" WHERE [VisID]= " & strVisID & " AND [QAID]= " & strQaID & ";"

Debug.Print strSQL

Set db = CurrentDb()
Set qdf = db.CreateQueryDef("", strSQL)
Set rst = db.OpenRecordset("SELECT QAID, VisID, EligiblePts, EarnedPts,
OverallScore" & _
" FROM QA_PROV_VISITS_T")

DoCmd.RunSQL strSQL

db.Close
qdf.Close

End Sub
 
M

Marshall Barton

xRoachx said:
The following sub updates values in a table and is functioning correctly
except for one small problem. The dblOverallScore is rounding up when the
field is updated in the table. The VB variable is a double and the field
type is a number (percent) that allows for 2 decimal places. For example, if
dblOverallScore is .97346545, the field in the table is updated to 100.00%
instead of 97.35%. Any ideas on why the rounding is occuring and how I can
correct this? Thx in advance!

Private Sub Form_AfterUpdate()

Dim lngEligiblePts As Long
Dim lngEarnedPts As Long
Dim dblOverallScore As Double
Dim lngVisID As Long
Dim lngQaID As Long
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

lngEligiblePts = txtEligiblePts.Value
lngEarnedPts = txtEarnedPts.Value
dblOverallScore = Round(txtOverallScore.Value, 4)
strVisID = VisID.Value
strQaID = QAID.Value


strSQL = "UPDATE QA_PROV_VISITS_T " & _
"SET [EligiblePts] = " & lngEligiblePts & "," & _
"[EarnedPts] = " & lngEarnedPts & "," & _
"[OverallScore] = " & dblOverallScore & "" & _
" WHERE [VisID]= " & strVisID & " AND [QAID]= " & strQaID & ";"
Click to expand...
[snip]


I think you are getting mixed up between the field
Type/Size and the Format property. Percent and number of
places are just formatting guides for when the field is
displayed in table sheet view and for setting a text box's
Format property when you drag the field from the field list
window to create a new form/report control.

It sounds like the field's Size is Integer or Long
(specified in the field properties on the lower part of the
table design window).
 
X

xRoachx

LOL! I love it when it is something simple that you spend quality time on.
Anyhow, you were correct. I changed the field size to double and it now
works correctly. Thx for the reply!

Marshall Barton said:
xRoachx said:
The following sub updates values in a table and is functioning correctly
except for one small problem. The dblOverallScore is rounding up when the
field is updated in the table. The VB variable is a double and the field
type is a number (percent) that allows for 2 decimal places. For example, if
dblOverallScore is .97346545, the field in the table is updated to 100.00%
instead of 97.35%. Any ideas on why the rounding is occuring and how I can
correct this? Thx in advance!

Private Sub Form_AfterUpdate()

Dim lngEligiblePts As Long
Dim lngEarnedPts As Long
Dim dblOverallScore As Double
Dim lngVisID As Long
Dim lngQaID As Long
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

lngEligiblePts = txtEligiblePts.Value
lngEarnedPts = txtEarnedPts.Value
dblOverallScore = Round(txtOverallScore.Value, 4)
strVisID = VisID.Value
strQaID = QAID.Value


strSQL = "UPDATE QA_PROV_VISITS_T " & _
"SET [EligiblePts] = " & lngEligiblePts & "," & _
"[EarnedPts] = " & lngEarnedPts & "," & _
"[OverallScore] = " & dblOverallScore & "" & _
" WHERE [VisID]= " & strVisID & " AND [QAID]= " & strQaID & ";"
Click to expand...
[snip]


I think you are getting mixed up between the field
Type/Size and the Format property. Percent and number of
places are just formatting guides for when the field is
displayed in table sheet view and for setting a text box's
Format property when you drag the field from the field list
window to create a new form/report control.

It sounds like the field's Size is Integer or Long
(specified in the field properties on the lower part of the
table design window).
Click to expand...
 
Top