Data Type Conversion Error - Percent

[jt463]

I am getting a 3421 Run-Time error 'Data type Conversion Error' on the following:

Dim IntDb As DAO.Database
Dim IntRst As DAO.Recordset

Set IntDb = CurrentDb
Set IntRst = IntDb.OpenRecordset("select * from LoanInterestRate")

Me.interest_ = Format(Me.interest_, "percent")

DoCmd.SetWarnings (False)
IntRst.Edit
IntRst!InterestRate = Me.interest_
IntRst.Update
IntRst.Close

DoCmd.Close

The IntRst!InterestRate data type is set to 'Number', Field Size is 'Integer', and format is 'Percent' with '2' Decimal Places.

The Me.interest_ property has a Format of 'General Number'.

Any help would be greatly appreciated.

 

[PHV]

Try either:
IntRst!InterestRate = Me.interest
or:
IntRst!InterestRate = Me.interest / 100

Me.interest_ is a formatted string !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jt463]

Thanks PHV.

I tried the following:

Private Sub SubmitButton_Click()

Dim IntDb As DAO.Database
Dim IntRst As DAO.Recordset

Set IntDb = CurrentDb
Set IntRst = IntDb.OpenRecordset("select * from LoanInterestRate")

DoCmd.SetWarnings (False)
IntRst.Edit
IntRst!InterestRate = Me.interest_ / 100
IntRst.Update
IntRst.Close

DoCmd.Close

End Sub

The suggestions allevieted my Run-Time error. So, thank you.

Now my problem is that I can not get my values to populate correctly to my table.

In other words, if my user places a value in the Me.interest field of 0.0625, and I use the IntRst!InterestRate=Me.Interest, then the table (with a number data type, integer field length, 3 decimal, and no formatting) shows 0! If the user enters 6.25 and the format is a percent, it shows as 600%.

The above code with a user entery of 6.25 returns 0 in my table.

Ultimately, what I want is for a user to be able to type in an interest rate and for me to be able to use that elsewhere in my code for calculations. If the field in the table only showing 0 actually stores the 0.0625, then that will work for my calculations.

I just am having a problem confirming on my table that the value is correct for my calculations.

Thanks again in advance.

 

[jt463]

Okay, I tried the following:

Dim intdb As DAO.Database
Dim intrst As DAO.Recordset
Dim test As Integer

Set intdb = CurrentDb
Set intrst = intdb.OpenRecordset("select * from LoanInterestRate")

test = (Me.interest_ / 100) * 500000

MsgBox ("Total is " & test & "."), vbOKOnly, "Test"

DoCmd.SetWarnings (False)
intrst.Edit
intrst!interestrate = test
intrst.Update
intrst.Close

DoCmd.Close

End Sub

And it calculated perfect! However, the value in the table field was still 0. So, I assumed that it just was rounding or something like that and ran a test calculation on the value in the table - the result of the calculation was 0!

I imagine it has to do with something I am doing wrong in formatting my table. But I can't think of what I am doing wrong where a data type of number with 4 decimal places will not show 0.0625!

Help!

 

[jt463]

I figured it out.

Two things I needed to change:

Dim test As [b]Variant[/b]

And in the table, I had to change my Field Size to Decimal.

So far everything has worked out.

Thanks for your time.