By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Type mismatch:Runtime error 13

P: 66
Expand|Select|Wrap|Line Numbers
  1. For r = 2 To 28
  2.  
  3.  
  4. Do While Len(Range("A" & r).Formula) > 0
  5. rs.AddNew
  6. rs.Fields(0) = Range("A" & r).Value
  7. rs.Fields(1) = Range("C" & r).Value
  8. 'Debug.Print r & ":(" & Range("D" & r).Value & ")"
  9. rs.Fields(2) = CLng(Range("D" & r).Value)
  10. rs.Fields(3) = Range("G" & r).Value
  11. rs.Fields(4) = Range("J" & r).Value
  12. 'rs.Fields(5) = CLng(Range("L" & r).Value)
  13. 'Debug.Print CLng(Range("L" & r).Value)
  14. rs.Fields(6) = CLng(Range("O" & r).Value)rs.Update
  15. r = r + 1
  16. Loop
  17. Next r
As seen in the attached file the first 2 record are inserted in the table.But Im getting Type mismatch error for the highlighted statements.

Can anyone Plz help me to rectify this..
Attached Files
File Type: txt outer3.txt (606 Bytes, 325 views)
Feb 12 '10 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,485
Please ask a clear question without requiring us to go round the houses just to work out what you want.

A good start in an Access / VBA forum is to make it clear you are working in Excel.
Feb 12 '10 #2

ADezii
Expert 5K+
P: 8,633
There are more than likely 2 possible explanations:
  1. The Values in the specific Column(s) where the Error(s) occur cannot be coerced to a LONG Data Type.
  2. The underlying Field in the Table representing the Recordset is not a comparable Data Type to LONG.
  3. If neither of these solve the problem, Attach the Spreadsheet if possible, and we'll have a look at it.
Feb 12 '10 #3

P: 66
Can u just go thru the attached file...

In this file the first 2 records are getting inserted but it gives me error for the columns "Gsteel" & last column "Qty"..

The datatype for both this columns is Long..So how do i rectify these code becoz i m getting error for these lines of code.
rs.Fields(5) = CLng(Range("L" & r).Value)
Debug.Print CLng(Range("L" & r).Value)
rs.Fields(6) = CLng(Range("O" & r).Value)
.
Feb 22 '10 #4

ADezii
Expert 5K+
P: 8,633
It may be getting hung up because of NULLS in the 2 Fields mentioned, try:
Expand|Select|Wrap|Line Numbers
  1. rs.Fields(5) = Nz(Range("L" & r).Value)
  2. rs.Fields(6) = Nz(Range("O" & r).Value)
Feb 22 '10 #5

P: 66
Thanks alot ADezii...but can u Plz tell me how to use Nz function in vba code as Excel does not support Nz fuction..

Can u Plz let me know...
Feb 22 '10 #6

ADezii
Expert 5K+
P: 8,633
At this point, I'm simply guessing. How about Uploading the Spreadsheet with the accompanying code, and I'll be glad to look at it.
Feb 22 '10 #7

NeoPa
Expert Mod 15k+
P: 31,485
Nz() is an Access function.

If you add the Microsoft Access library reference to your Excel project you will be able to use Nz().
Feb 22 '10 #8

P: 1
Add References some is missing..
Feb 22 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.