Insert Into DATE() does not work -gives wrong date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I found the following code in a previous post and I am trying to use this:

Private Sub MyProcedureName
Dim sSQL as string

sSQL = "INSERT INTO Dispositions([Date], [EventID], [Disposition]) " & _
"VALUES (" & DATE() & ", " & Me.EventID & ", 'Opened');"

CurrentDb.Execute sSQL

End Sub
Click to expand...

however, the DATE() in the Values portion of the statement does not
work. Access 97 kicks out the brackets following DATE, therefor the date
inserted is incorrect.

Any ideas how one might correct the date?

Would there be a correct solution such as:

Dim InsertDate as string

InsertDate = DATE()


Cheers
 
NoSpam said:
I found the following code in a previous post and I am trying to use this:
Private Sub MyProcedureName
Dim sSQL as string

sSQL = "INSERT INTO Dispositions([Date], [EventID], [Disposition]) " & _
"VALUES (" & DATE() & ", " & Me.EventID & ", 'Opened');"

CurrentDb.Execute sSQL

End Sub
Click to expand...
Click to expand...

Dates need to be delimited with #.
sSQL = "INSERT INTO Dispositions([Date], [EventID], [Disposition]) " & _
"VALUES (#" & DATE() & "#, " & Me.EventID & ", 'Opened');"

CurrentDb.Execute sSQL
Click to expand...
Click to expand...
 
Rick Brandt said:
NoSpam said:
I found the following code in a previous post and I am trying to use this:
Private Sub MyProcedureName
Dim sSQL as string

sSQL = "INSERT INTO Dispositions([Date], [EventID], [Disposition]) " & _
"VALUES (" & DATE() & ", " & Me.EventID & ", 'Opened');"

CurrentDb.Execute sSQL

End Sub
Click to expand...
Click to expand...

Dates need to be delimited with #.
sSQL = "INSERT INTO Dispositions([Date], [EventID], [Disposition]) " & _
"VALUES (#" & DATE() & "#, " & Me.EventID & ", 'Opened');"

CurrentDb.Execute sSQL
Click to expand...
Click to expand...
Click to expand...


Another option would be not to have the Date function outside of the quotes:

sSQL = "INSERT INTO Dispositions([Date], [EventID], [Disposition]) " & _
"VALUES (DATE(), " & Me.EventID & ", 'Opened');"
 
Back
Top