Type Conversion Error

[misstry]

With the file I am working with I use this SQL to move information from one table to another. The RO field is a number but used to be text in one table and has always been a number in the other. I had to change my file to number. However, I am new at this and it is giving me a Type Conversion Error and will not add to the table. I am pretty sure it is to do with the RO field. Does this have something to do with the quotations and apostrophes for the that particular field since it is a number? It did work until I had to adjust the field from text.

DoCmd.RunSQL "INSERT INTO [ROs To Be Deleted From ScoreCard] VALUES(" & Me.RO & ",'" & Me.SRCM & "','" & Me.CM_ID & "','" & Me.Commodity_Mgr & "','" & Me.MPN & "','" & Me.ME & "','" & Me.Serial_Number & "','" & Me.VID & "','" & Me.Vendor & "','" & Me.ScrubCode & "','" & Me.ScrubComments & "','" & Me.SrComments & "',#" & Date & "#);"

Thank you in advance.

 

[Micron]

I don't really understand the issue or question. If anything you have single quotes around is going into a text field and the value is text, Null or an empty string, that is OK (where the form control contains Null, then as long as Nulls are allowed in the table field). Anything not single quoted will get treated as a number, which shouldn't raise an error if you're trying to insert a number into a text field. Of course the exceptions would be scientific notation including letters, or text that looks like a number, but doesn't fit the table field; e.g. the textual number is too large for the field data type, or contains decimals which are not allowed by the field data type, etc. The issue probably lies in the textual number not fitting into the field you're trying to put it in. Since you don't seem to be using any conversion function, I've omitted that as a possibility.

To trouble shoot, put a break on the line after this and output your sql string to the immediate window, where your control references will be replaced by the values in the form controls. You can also copy/paste that into a new query and switch to datasheet view. Often, the offending part is higlighted (or right beside it) at that point.

Some other issues that don't really look like they're related to conversion errors:
- ME is a bad name for a field or control. If you must use such a reserved word, then put square brackets around it. Me.[ME]. Even then, no guarantees. Suggest you research naming conventions and adopt one.
- probably not necessary, but consider ...Date() & "#);" VBA constructed sql statements don't require ending in ; but if you do need to copy from the immediate window, you have to remember to add it in query sql view.

 

[xenou]

Output the sql string to the immediate window so you can test it directly (as raw sql). This is sometimes much easier to figure out. You should be able to run your sql directly in new query - SQL view. Also if you are lucky access will place the cursor directly under the problem part of the query.

dim strSQL as String
strSQL = "INSERT INTO [ROs To Be Deleted From ScoreCard] VALUES(" & Me.RO & ",'" & Me.SRCM & "','" & Me.CM_ID & "','" & Me.Commodity_Mgr & "','" & Me.MPN & "','" & Me.ME & "','" & Me.Serial_Number & "','" & Me.VID & "','" & Me.Vendor & "','" & Me.ScrubCode & "','" & Me.ScrubComments & "','" & Me.SrComments & "',#" & Date & "#);"
debug.print strSQL

the results will output to the immediate window if you use the above debug.print statement.

Note that the RO field looks correct if the field datatype is number and the form field also has a number so the problem *could* be elsewhere. Also as noted always put brackets around fields with names that are the same as VBA keywords, so me.ME should be me.[ME] (and these types of names are best avoid for field names as a rule).