I have a SQL String that converts the Ordinal Date stored on the Server, but when I add the string to VBA I get the Run Time Error that the Column or global variable doesn't exist.
When I use the string in Microsoft Query it works just fine. Please note I have almost no knowledge of SQL.
This is the SQL String I am using to convert from YYYYDD to MMDDYYYY
decimal(replace(char(date(timestamp_format(digits(CFMAST.CFDOB7),'YYYYDDD')),usa),'/',''),8,0)
Here is how I am implementing it into VBA for Excel
strSQL="SELECT decimal(replace(char(date(timestamp_format(digits(CFMAST.CFDOB7),'YYYYDDD')),usa),'/',''),8,0) FROM CNCTTP08.JHADAT842.CFMAST "
strSQL = strSQL & " WHERE (CFMAST.CFDOB7<>0) AND (CFMAST.CFDOB7<>1800001)"
When I run the query through Microsoft Query I get the expected result where it formats all of the dates from CFMAST.CFDOB7 as MMDDYYYY, but when I run the string in VBA I get the following error:
[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0206 - Column or global variabl CFDOB7 not found.
The problem may be that you are using a three part name for the table CNCTTP08.JHADAT842.CFMAST. Remove the system name CNCTTP08 and just use the schema and table JHADAT842.CFMAST. Your ODBC connection should get you to the correct system.
Another issue may be that DB2 for i doesn't really like correlated names unless one has been explicitly defined. So if you really do need the three part name, add a correlation identifier in the from clause like this:
FROM CNCTTP08.JHADAT842.CFMAST AS CFMAST