CODEWITHSUNDEEP
ms-accesslotus-notesms-access-2010lotus-dominolotusscript
Mike Jackson
Mike Jackson

Reputation: 427

What is the SQL Query Syntax to write to a Date/Time Data Field in MS Access from LotusScript?

I have a LotusScript (8.5.3) routine that writes Notes data from a view (8.5.3) to a Microsoft Access 2010 database using ODBC. I'm using an SQL statement similar to the one below to write the data. The problem I'm having is will the Syntax to get a Notes Date/Time value to an Access 2010 Date/Time value.

strSQL=|INSERT INTO DATASHEET("DocumentNumber","RevisionNumber","RevisionDate") VALUES('| >+ docnum + |','| + docrevnum + |',{d'| + revdate.DateOnly + |'}|

When I execute this, I get the following error:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query >expression 'd '06/23/2010'}'

I'm sure my syntax is wrong, but I have tried multiple syntaxes that I have found referenced on the Web and none seem to work.

Can someone who really knows what the syntax should be and has code that is working, please give me the correct Syntax.

UPDATE: I tried the following Syntax:

strSQL=|INSERT INTO DATASHEET("DocumentNumber","RevisionNumber","RevisionDate") VALUES('| >+ docnum + |','| + docrevnum + |',#| + revdate.DateOnly + |#|

When I execute this, I get the following error:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '#'.

Thanks,

MJ

Upvotes: 2

Views: 1534

Answers (1)

Mike Jackson
Mike Jackson

Reputation: 427

HansUp's comment was the answer. Below is what the Syntax ended up being:

The problem I had when I first ran it was that it came across a NULL date, so it didn't know how to process it. So, I mad the following changes:

If revdate.DateOnly="" then rdate="01/01/1900" 'This is Access' equivalent to null date else rdate=revdate.DateOnly end if

strSQL=|INSERT INTO DATASHEET("DocumentNumber","RevisionNumber","RevisionDate") VALUES('| >+ docnum + |','| + rdate + |',#| + revdate.DateOnly + |#|

Thanks for the help.

MJ

Upvotes: 2

Related Questions