SQL = "INSERT INTO COMPANIES ( Shift, Operator, Date, DTCode, Line,
Hours ) " & _
"SELECT '" & NewShift & "', '" & NewOp & "', " & NewDate & ", " & NewDT
& ", '" _
& NewLine & "', " & NewHr & ""
This looks like a total mess: using the SELECT syntax really needs a FROM
clause. If you want to pass literal values, then use the VALUES syntax.
If you want to pass literal values, you simply have to handle correct
formatting and delimiters yourself explicitly. You may have unbalanced
quote marks.
Date is a reserved word in just about very language you'll come across
and is a rubbish name for a column. Same probably applies to Line and
Hours although they actually might be legal. Enclose them in [brackets]
just to be sure.
Try this:
SQL = "INSERT INTO Companies " & vbNewLine & _
"(Shift, [Operator], [Date], " & _
"DTCode, [Line], [Hours]) " & vbnewline & _
"VALUES ( " & SQLString(NewShift) & ", " & vbnewline & _
" " & SQLString(NewOp) & ", " & vbnewline & _
" " & SQLDate(NewDate) & ", " & vbnewline & _
" " & Format(NewDt,"0.00") & ", " & vbnewline & _
" " & SQLString(NewLine) & ", " & vbnewline & _
" " & Format(NewHr, "0000000") & vbnewline & ")"
Debug.Assert vbYes=MsgBox(SQL, vbYesNo, "Is this OK?"
The conversion functions look like
public function SQLString(SomeString as String) as string
somestring = """" & _
Replace(SomeString, """", """""") & _
""""
end function
public function SQLDate(SomeDate as variant) as string
' Jet is not regionally aware: use international
' or USA date formats only!!
const jetDateFormat = "\#yyyy\-mm\-dd\#"
if isnull(SomeDate) Then
SQLDate = "NULL"
else
SQLDate = Format(SomeDate, jetDateFormat)
End if
end function
Formatting numbers is probably trivial, but that is up to you of course.
Hope that helps
Tim F
