INSERT INTO problem trying to use general date

[plh]

I am trying to construct an SQL sting that includes a date and time entry. The
second field in the table tblTransactions is of type "Date/Time" with the format
of "General Date":

strSQL = "INSERT INTO tblTransactions VALUES " & _
"(" & Me.txtID.Value & ", " & Me.txtOEDateOut.Value & ", " &
Me.cmbOESendees.Value & ")"

As its name implies Me.txtOEDateOut is a text box control.

This results in:
INSERT INTO tblTransactions VALUES ( 8, 12/4/2006 10:12:02 AM, 5)

I get this error message:
Syntax error (missing operator) in query expression '12/4/2006 10:12:02 AM'.

I have the feeling that it has to do with the spaces in '12/4/2006 10:12:02 AM'
but what do I do about that? I would have thought that a General Date could
include both the date and the time.
Thank You,
plh

 

[Stefan Hoffmann]

hi,

I am trying to construct an SQL sting that includes a date and time entry. The
second field in the table tblTransactions is of type "Date/Time" with the format
of "General Date":

The format of "General Date" just controls how the date/time value is
displayed (formatted), it does not have any influence on how it is stored.

strSQL = "INSERT INTO tblTransactions VALUES " & _
"(" & Me.txtID.Value & ", " & Me.txtOEDateOut.Value & ", " &
Me.cmbOESendees.Value & ")"

You need to generate a date/time literal.

This results in:
INSERT INTO tblTransactions VALUES ( 8, 12/4/2006 10:12:02 AM, 5)

A valid literal would be:

#12/4/2006 10:12:02: AM#

Use the Format function to get a valid date/time literal:

Format(ADateTime, "\#m\/d\/yyyy hh\:nn\:ss\#")

e.g.

strSQL = "INSERT INTO tblTransactions VALUES " & _
"(" & txtID.Value & ", " & _
Format(txtOEDateOut.Value, "\#m\/d\/yyyy hh\:nn\:ss\#") & ", " & _
cmbOESendees.Value & ")"


mfG
--> stefan <--

 

[Brendan Reynolds]

You need '#' symbols around the date/time value ...

strSQL = "INSERT INTO tblTransactions VALUES " & _
"(" & Me.txtID.Value & ", #" & Me.txtOEDateOut.Value & "#, " &
Me.cmbOESendees.Value & ")"

 

[plh]

You need '#' symbols around the date/time value ...

strSQL = "INSERT INTO tblTransactions VALUES " & _
"(" & Me.txtID.Value & ", #" & Me.txtOEDateOut.Value & "#, " &
Me.cmbOESendees.Value & ")"

Worked like a charm, Thank You! I had the feeling it was something simple.
-plh