Datefield from variable into a datefield in an access b

30 Jun 2006 9:15 PM

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

30 Jun 2006 4:58 AM

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

Show quoteHide quote

"reidarT" <rei***@eivon.no> schreef in bericht
news:OT4%231E8mGHA.1204@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
>
>
>
>

30 Jun 2006 8:03 AM

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:
Show quoteHide quote

> 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
>
>
>
>