Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 831

# Updating Percent Field Using VBA Calculation

I am trying to add a new record to a table (rcFTDSb [recordset]). The field being updated has a datatype of number and format of percent and decimal of Auto.  The variable <Vr1wpct> is calculating correctly, but the table field <![r1wpct]> is still showing 0 only.  Any answer on how I can get the table field <![r1wpct]> to show the result of the variable <Vr1wpct> which in my test is .50.  Thanks.

Here is a sampling of the relevant code:

Dim w1 As Integer
Dim r1 As Integer
Dim Vr1wpct As Variant
Dim rcFTDSb As Recordset
Dim rcEndB As Recordset

w1 = 0
r1=0
Vr1wpct = 0

If rcFTDSb!race = 1 And rcFTDSb!win > (CCur(0)) Then
w1 = w1 + 1
End If

If r1 >= 1 Then
Vr1wpct = w1 / r1 ‘should be <.50 = 5 / 10> this is working fine
End If

With rcEndB
![r1wpct] = Vr1wpct ‘<0 = .50> ‘problem. The field is not recognizing .50
End With
rcEndB.Update
0
Mahonek
2 Solutions

Commented:
Hi Mahonek,
> field being updated has a datatype of number

There is not really such a thing as a 'number' data type.  I suspect that you have it set to either Integer or Long, which
cannot hold anything other than a whole number.  If this is the case, what you need is Single or Double.

Regards,

Patrick
0

Commented:
Try this:

Dim w1 As Integer
Dim r1 As Integer
Dim Vr1wpct As Variant
Dim mydb as Database, myrs1 as Recordset, myrs2 as Recordset
'Dim rcFTDSb As Recordset
'Dim rcEndB As Recordset
Set mydb = currentdb()
set myrs1=mydb.openrecordset("cdFTDSb")
set myrs2=mydb.openrecordset("rcEndB")
myrs1.movelast
myrs1.movefirst
' do not create variables with the same name as your tables.  You must first dimension variables and then Set 'them to your actual tables.  Then you populate the VB variable by moving last and then moving first.  I cannot 'tell from your code what you are trying to do.  After setting r1 to zero, you will never get into the IF statement.
'Look at VB Help (ctl-G) - AnswerWizard - Openrecordset

w1 = 0
r1=0
Vr1wpct = 0

If rcFTDSb!race = 1 And rcFTDSb!win > (CCur(0)) Then
w1 = w1 + 1
End If

If r1 >= 1 Then
Vr1wpct = w1 / r1 ‘should be <.50 = 5 / 10> this is working fine
End If