setting a date value to sql server

[Marius Kaizerman]

I'm trying to set the current time using now() to a date field on a table
which is in sql server.
I've tried the following syntax:

DoCmd.RunSQL "update openclosepos set closedate= '" & now() & "' where
openid= '" &
tmpopen & "'"

but then I get an error which says that it cannot enter a string value into
a date field.

I've tried:

DoCmd.RunSQL "update openclosepos set closedate= #" & now() & "# where
openid= '" & tmpopen & "'"

but then I get an error which says that there is a syntax problem near the
#.

What is the right syntax ? Please keep in mind that closedate is defined as
a date field.

Thanks!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Now() returns the date and time in this format:

1/15/2004 9:28:26 AM

SQL Server likes its date/time data formatted like this:

'2004-01-15 09:28:26'

If the time were 9 PM the time would be in 24-hour format: 21:28:26.

So you can change your SQL string to this:

strSQL = "update openclosepos set closedate= '" & _
Format(now(),"yyyy-mm-dd hh:mm:ss") & "' " & _
"where openid= '" & tmpopen & "'"

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAbQXoechKqOuFEgEQJU1ACgq30g43zKxdqEZ1FX8KfZ3k EjPGoAnRKQ
+95a9jGvRma++ytY5fffsl3h
=aa7B
-----END PGP SIGNATURE-----
Marius Kaizerman wrote:

I'm trying to set the current time using now() to a date field on a table
which is in sql server.
I've tried the following syntax:

DoCmd.RunSQL "update openclosepos set closedate= '" & now() & "' where
openid= '" &
tmpopen & "'"

but then I get an error which says that it cannot enter a string value into
a date field.

I've tried:

DoCmd.RunSQL "update openclosepos set closedate= #" & now() & "# where
openid= '" & tmpopen & "'"

but then I get an error which says that there is a syntax problem near the
#.

What is the right syntax ? Please keep in mind that closedate is defined as
a date field.

Thanks!