SQL string with date doesn't work

[Andreas]

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

 

[Glenn Siswick]

Hi Andreas,

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

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

Regards,
Glenn

 

[Marshall Barton]

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)

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"

 

[Andreas]

perfect, I love the usenet! Now it works,

Thank you very much!

Andreas