ODBC Scalar Functions

[boreal]

I'm unable to get the function TIMESTAMPADD to work in either MS Query
(v.11.5510.6408 for Office 2003) or Access 2000 when querying an AS400 system
using IBM's Client Access Express ODBC Driver. Is it not supported? What is
supported?

MS Query:
Setting a column definition with
TIMESTAMPADD(SQL_TSI_HOUR,14,TIMESTAMP(CURDATE(),CURTIME())) results in error
"SQL0204 - TIMESTAMPADD in *LIBL type *N not found."

So, since Query didn't seem to recognize TIMESTAMPADD as a function, I tried:
{fn TIMESTAMPADD(SQL_TSI_HOUR,14,TIMESTAMP(CURDATE(),CURTIME()))}
This resulted in the error "Syntax Error in date."

I tried DATEADD("h",14,TIMESTAMP(CURDATE(),CURTIME()))
and DATEADD("h",14,now())
but I received the error "Column 'h' not in specified tables"

Access:
None of these functions work in Access either. When I try the latter
DATEADD, I get "ODBC--call failed. [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]SQL0418 - Use of parameter marker not valid."

Help.

 

[Bob Buckland ?:-\)]

Hi Boreal,

You may want to post this in the MS Access newsgroup as well
(link below).

=======
I'm unable to get the function TIMESTAMPADD to work in either MS Query
(v.11.5510.6408 for Office 2003) or Access 2000 when querying an AS400 system
using IBM's Client Access Express ODBC Driver. Is it not supported? What is
supported?

MS Query:
Setting a column definition with
TIMESTAMPADD(SQL_TSI_HOUR,14,TIMESTAMP(CURDATE(),CURTIME())) results in error
"SQL0204 - TIMESTAMPADD in *LIBL type *N not found."

So, since Query didn't seem to recognize TIMESTAMPADD as a function, I tried:
{fn TIMESTAMPADD(SQL_TSI_HOUR,14,TIMESTAMP(CURDATE(),CURTIME()))}
This resulted in the error "Syntax Error in date."

I tried DATEADD("h",14,TIMESTAMP(CURDATE(),CURTIME()))
and DATEADD("h",14,now())
but I received the error "Column 'h' not in specified tables"

Access:
None of these functions work in Access either. When I try the latter
DATEADD, I get "ODBC--call failed. [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]SQL0418 - Use of parameter marker not valid."

Help. >>
--
LLet us know if this has helped you,

Bob Buckland ?
MS Office System Products MVP
*courtesy is not expensive and can pay big dividends8

A. Specific newsgroup/discussion group mentioned in this message:
news://msnews.microsoft.com/microsoft.public.access
or via browser:
http://microsoft.com/communities/newsgroups/en-us/?dg=microsoft.public.excel

B. MS Office Community discussion/newsgroups via Web Browser
http://microsoft.com/office/community/en-us/default.mspx
or
Microsoft hosted newsgroups via Outlook Express/newsreader
news://msnews.microsoft.com