Solved

# Updating Percent Field Using VBA Calculation

Posted on 2006-05-28
825 Views
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
Question by:Mahonek

LVL 92

Accepted Solution

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

LVL 44

Assisted Solution

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

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

### Suggested Solutions

This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

#### 759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!