SQL string with date doesn't work

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andreas - 09 May 2008 13:50 GMT

Hello,

I have a sql-string in my vba code but I think I made a mistake in the
syntax. I tried several things, but get always an error.

Further: the database is used in several countries, do I have to
convert the date (here in Germany it is stored like below)?

sql = "UPDATE tab_Cards_2008 SET num_complete = 1 " & _
"WHERE dat_DateColl = 01.01.1911 OR num_1=99"
DoCmd.RunSQL (sql)

Thank you very much for your help!

Andreas

Reply to this Message

Glenn Siswick - 09 May 2008 14:32 GMT

Hi Andreas,

Take a look at the following link for a full explanation:

http://allenbrowne.com/ser-36.html

Regards,
Glenn

> Hello,
>
[quoted text clipped - 11 lines]
>
> Andreas

Reply to this Message

Marshall Barton - 09 May 2008 14:42 GMT

>I have a sql-string in my vba code but I think I made a mistake in the
>syntax. I tried several things, but get always an error.
[quoted text clipped - 5 lines]
> "WHERE dat_DateColl = 01.01.1911 OR num_1=99"
> DoCmd.RunSQL (sql)

Date literals must ben enclosed in # signs and be in either
USA or an unambiguous style.

A way to achieve that is to use this kind of syntax:

"WHERE dat_DateColl = #1911-01-01# OR num_1=99"

If you have a date variable instead of a literal, then use:

"WHERE dat_DateColl = " & Format(dtvar, "\#yyyy-m-d\#") _
& " OR num_1=99"

Signature

Marsh
MVP [MS Access]

Reply to this Message

Andreas - 09 May 2008 15:04 GMT

perfect, I love the usenet! Now it works,

Thank you very much!

Andreas

> Date literals must ben enclosed in # signs and be in either
> USA or an unambiguous style.
[quoted text clipped - 11 lines]
> Marsh
> MVP [MS Access]

Reply to this Message