Data type error in SQL query (Excel VBA)

[jirkap]

Hi,

I have a quite complex UserForm and using SQL queries I retrieve from and also save data to another workbook (unfortunately I cannot use Access db in this case).

There are three particular textboxes in the form with linked checkboxes. Normally, users enter numbers to the fields and everything saves fine to the external workbook. But when the checkbox is ticked, it changes linked textbox value to "Unknown" and I get an error when saving that says something like "Data type error" (I can't post the actual expression as I have excel in Czech). Anyway, I checked and the query seems to generate ok. To make it even more complicated, there is one of the fields that saves without error under same conditions.

Here goes the code I use to generate SQL query:

 Dim SQL As String
    SQL = "INSERT INTO [Report$A2:AM50000] ("
 
    Dim i As Control
    For Each i In Me.controls
        If TypeName(i) = "TextBox" Or TypeName(i) = "ComboBox" Then ' Take only Textboxes & Combos into account
            If i.Value <> e Then SQL = SQL & i.Name & "," ' Only select not empty fields
        ' ElseIf TypeName(i) = "CheckBox" Then
        End If
 
    Next i
    SQL = Mid(SQL, 1, Len(SQL) - 1) & ") VALUES(" ' Remove last space & comma
 
    Dim j As Control
    For Each j In Me.controls
 
         If TypeName(j) = "TextBox" Or TypeName(j) = "ComboBox" Then
 
            If j.Value <> e Then
 
                Select Case IsNumeric(j.Value)
                    Case False
                            SQL = SQL & "'" & j.Value & "'" ' Add single quotes around strings
                    Case True
                            SQL = SQL & j.Value
                End Select
                SQL = SQL & ","
            End If
        ' ElseIf TypeName(i) = "CheckBox" Then
        End If
    Next j
    SQL = Mid(SQL, 1, Len(SQL) - 1) & ")"   ' Remove last comma

I tried CStr(textbox.text) but did not work. Does anyone know what's wrong?