Copy a negative number from Excel to SQL using VBA results in an error (converting varchar to numeric)

[robert_645]

Hello,

I have a vba module to update a SQL database. One of the data is numeric. If the data is a positive number, there is no problem, but if the number (zscore) in Excel is negative an error pops up: Error converting datatype varchar to numeric.

zscore = Range("J" & mytel99)


rc.Open "INSERT INTO dbo.QHSE_3rdline_history (RvA_Nr, RvA_Letter, Afdeling, Datum, Organisator, NummerRO, Eenheid, ALC, RO, Interpretatie) " _
        & "VALUES ('" & rva1 & "', '" & rva2 & "', '" & Range("B" & mytel99) & "', '" & datum & "', '" & Range("B1") & "', '" & Range("B2") & "', '" & Range("G" & mytel99) & "', '" & Range("H" & mytel99) & "', '" & Range("I" & mytel99) & "', '" & Range("K" & mytel99) & "')", con
rc.Open "UPDATE dbo.QHSE_3rdline_history SET [Matrix] = '" & Range("C" & mytel99) & "', [Component] = '" & Range("D" & mytel99) & "', [Zscore] = '" & zscore & "' WHERE ([RvA_Nr] = '" & rva1 & "' AND [RvA_Letter] = '" & rva2 & "' AND [Afdeling] = '" & Range("B" & mytel99) & "' AND [Datum] = '" & datum & "' AND [Organisator] = '" & Range("B1") & "' AND [NummerRO] = '" & Range("B2") & "' AND [Eenheid] = '" & Range("G" & mytel99) & "' AND [ALC] = '" & Range("H" & mytel99) & "' AND [RO] = '" & Range("I" & mytel99) & "' AND [Interpretatie] = '" & Range("K" & mytel99) & "')", con

Does anybody knows how to get negative data in a numeric SQL table?
Thanks in advance!

 

[Derek Brown]

Not sure that I fully understand the problem but have you looked and the conversion functions? e.g Clng, Cstr, Cint etc.