VBA INSERT INTO converts text recordset into integer problem

Question

Got a problem with my INSERT INTO statement. My recordset's Column 3 has a string composed of two integers separated by a dash, like this '4-5'. When I check in the table, MyTable shows it as -1. I tested setting the table's column as text and memo and that did not work.

I suspect it has something to do with the INSERT INTO statement. This is what it looks like, 

DoCmd.RunSQL "INSERT INTO MyTable (Column1,Column2,Column3,Column4) VALUES (" & rst2.Fields(0).Value & "," & rst2.Fields(1).Value & "," & rst2.Fields(2).Value & "," & rst2.Fields(3).Value & "," & rst2.Fields(4).Value & ")"

I printed the recordset in my immediate window and it has the '4-5' format in it. Also tried separating these as ' 4 - 5 ' and it would still do -1. 

How can I keep that recordset as string when inserting it into the MyTable?

Thanks for the help.

Answer 1

Assuming that rst2.Fields(2) is the 3rd column:

... & ",'" & rst2.Fields(2).Value & "'," & ...

This encloses the value in single quotes, forcing Access to interpret it as a text value.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Works perfect!!!! Thank you so much!