Runtime error 3421,Data type conversion error

[shalskedar]

I want to transfer the excel data to th Db ..thru the VBA code.
In the DB there is a table called outer2 containing 1 of the columns as "Upvc" whose format is set to Long integer.

When i try to transfer the data from my excel sheet to Db,I get Data type conversion error..

What do i do to eliminate this error.Below is my vba code for transfer of data frm excel to access

sub kd()
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Dim rr As Range
Dim i As Integer
Dim r As Long
 
Set db = DAO.DBEngine.OpenDatabase()
 
Set rs = db.OpenRecordset("outer2", dbOpenDynaset)
MsgBox "done"
 
For r = 2 To 28
 
 
rs.AddNew
rs.Fields(1) = Range("A" & r).Value
rs.Fields(2) = Range("C" & r).Value
rs.Fields(3) = Range("D" & r).Value//error here
 
rs.Update
 
Next r
 
End Sub

[NeoPa]

It might be a good idea for a Data Type Conversion Error to include the offending data.

Please also remember to use the CODE tags. They are not optional and as a full member (next time you post) you will be likely to receive an infraction for further such posts.

[TheSmileyCoder]

Chances are you have some "offending" data in the field.

1) Start by opening the excel sheet and make sure your input data is correct.
If that doesn't work
2) Change your code to:

debug.print r & ":(" & Range("D" & r).Value & ")"
rs.Fields(3) = clng(Range("D" & r).Value)

When the code fails, click debug, and look at the immediate window (Ctrl-G) and simply read the largest r value, and the value in parenthesis. The value in parenthesis will be the "problem" value.



In the end, if your sure your data is valid, you could do a clng in front of it (Conversion to long)

rs.Fields(3) = clng(Range("D" & r).Value)

[shalskedar]

Thanks alot.As suggested i tried out debugging & the value which i get for the 5th record is 505.6788 in the immediate window.

I think i m getting the error for this value, as the first 2 records fetch the value as 1900 & 1600.The other 2 records are 'having blank values..

So where do i need to make the changes i.e i mean in the table definition what format property do i need to set .Plz guide me...

[NeoPa]

It would really be handy if you posted the example data as requested. I don't think this value would have a problem converting to Long Integer, but I haven't done exactly this before so maybe it does. If we could see more of the data (not all necessarily, but some lines which must include the data it failed on) then we could have a better chance of identifying the problem.

In the mean-time have you tried Smiley's suggestion of :

CLng(Range("D" & r))