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