Database - How can I work with dates correctly?

[Nosrej]

please help me with this..please tell what is wrong here..here is my code;

Private Sub cmdSave_Click()
Dim db_file As String
Dim statement As String
Dim conn1 As ADODB.Connection
Dim ctl As Control
Dim sDateString As Date
 
' Date Format
'sDateString = Format(DTCalendar.value, "mm/dd/yyyy")
 
' Get the data.
db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "DB.mdb"
 
' Open a connection.
Set conn1 = New ADODB.Connection
conn1.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
conn1.Open
 
' Compose the INSERT statement.
statement = "INSERT INTO tblDPSR " & _
"(LineNo, Shift, Date, PreparedBy, ItemCodes, Weight, StartingNo, LastNo, Quantity, TotalHPE1) " & _
" VALUES (" & _
"'" & cmbLine.Text & "', " & _
"'" & cmbShift.Text & "', " & _
"'" & DTCalendar.value & "', " & _
"'" & cmbPrepared.Text & "', " & _
"'" & cmbIC1.Text & "', " & _
"'" & txtWeight1.Text & "', " & _
"'" & txtStarting1.Text & "', " & _
"'" & txtLast1.Text & "', " & _
"'" & txtQ1.Text & "', " & _
"'" & txtTotalHPE1.Text & "'," & _
")"
 
' Execute the statement.
conn1.Execute statement, , adCmdText
'conn1.Execute ("INSERT INTO tblDPSR(Date) VALUES (" & "'" & DTCalendar.value & "#,)")
 
' Close the connection.
conn1.Close
 
' Clear the TextBoxes.
For Each ctl In Controls
If TypeOf ctl Is TextBox Then
ctl.Text = ""
End If
Next ctl
 
End Sub

But as i click my save button, this is the error message:
Syntax error in INTO statement
With that error, highlights the line: conn1.Execute statement, , adCmdText

What could be my error? Please help me

[Hack]

Split into its own thread and moved to VB6 And Earlier

[techgnome]

Most of your questions can be answered here - http://www.vbforums.com/showthread.php?t=337051 including why you shouldn't use "date" as a field name (big ol' hint: it's a reserved word... which is addressed in the linked thread)... might also be work reading through the section on parameters.

-tg

[DataMiser]

Yes you should not use Date as a field name and if you do then you must surround it with [] in your sql statements as in [date]. The same is true for all reserved words as well as field names which contain spaces.

Also Access requires you to use # as a delimiter for date fields rather than '. ' is used for text fields and nothing is used on numeric fields.


If your fields are defined as the types the names would suggest then you have several errors in that SQL Statement