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
  • Last Modified:

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


rcEndB.AddNew
        With rcEndB
            ![r1wpct] = Vr1wpct ‘<0 = .50> ‘problem. The field is not recognizing .50      
        End With
rcEndB.Update
0
Mahonek
Asked:
Mahonek
2 Solutions
 
Patrick MatthewsCommented:
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
 
GRayLCommented:
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


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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now