Datefield from variable into a datefield in an access b

[reidarT]

I use a DateTimePicker to get a date.
I want to add this date into an access table.
I use a variable like
Dim dteDato As Date = Format(Me.txtDato.Value, "Short date")

In the sql-statement I use
INSERT INTO tblToDo ( Dato, Bruker, Utfort, Melding, MeldingFra ) " & _
"SELECT " & dteDato & ", '" & strSaksbehandler & "', -1, '" & strMelding &
"', 'Fra Peres'", con)
....

The date shows as #7/1/2006#, but when I run the sql I get a syntax-error
If I replace the dteDato in the sql with #7/1/2006# it works OK.

reidarT

[Cor Ligthert [MVP]]

Reidar,

Especially datetimes should be handled with parameters.

See here this simple sample.

http://msdn.microsoft.com/vstudio/express/vwd/

Be aware that this is the most simple sample, there are more samples on our
website.

I hope this helps,

Cor

"reidarT" <re****@eivon.no> schreef in bericht
news:OT****************@TK2MSFTNGP03.phx.gbl...

I use a DateTimePicker to get a date.
I want to add this date into an access table.
I use a variable like
Dim dteDato As Date = Format(Me.txtDato.Value, "Short date")

In the sql-statement I use
INSERT INTO tblToDo ( Dato, Bruker, Utfort, Melding, MeldingFra ) " & _
"SELECT " & dteDato & ", '" & strSaksbehandler & "', -1, '" & strMelding &
"', 'Fra Peres'", con)
...

The date shows as #7/1/2006#, but when I run the sql I get a syntax-error
If I replace the dteDato in the sql with #7/1/2006# it works OK.

reidarT

[R. MacDonald]

Hello, reidarT,

Try replacing dteDato in your SQL with Format(dteDato, "#yyyy-MM-dd#").

Note that I am recommending using a fixed date format. As I recall,
Access by default uses m/d/y, but it also happily accepts the (far
better) ISO format.

Groetjes,
Randy
reidarT wrote:

I use a DateTimePicker to get a date.
I want to add this date into an access table.
I use a variable like
Dim dteDato As Date = Format(Me.txtDato.Value, "Short date")

In the sql-statement I use
INSERT INTO tblToDo ( Dato, Bruker, Utfort, Melding, MeldingFra ) " & _
"SELECT " & dteDato & ", '" & strSaksbehandler & "', -1, '" & strMelding &
"', 'Fra Peres'", con)
...

The date shows as #7/1/2006#, but when I run the sql I get a syntax-error
If I replace the dteDato in the sql with #7/1/2006# it works OK.

reidarT