This is Financials 2.00a I’m attempting to get XLODBC to return Cust. Ledger Entry records for a given date range for invoices and credit notes to an XL sheet. The SQLExecQuery querytext is as follows: queryText:=“SELECT ““Customer No_””, ““Document No_””, ““Posting Date””, ““Salesperson Code””, ““Amount (LCY)””, ““Sales Cost (LCY)””, ““Profit (LCY)””, ““Country Code”” " + _ “FROM ““Cust_ Ledger Entry”” " + _ “WHERE ((”“Document Type””=3) " + _ “OR (”“Document Type””=2))" + _ “AND (”“Posting Date”" >= ‘" + Range(“Front_Sheet!Start_Text”) + "’)" + _ “AND (”“Posting Date”" <= ‘" + Range(“Front_Sheet!End_Text”) + "’)" + _ “ORDER BY ““Posting Date””, ““Customer No_”” " where Start_Text contains something like 01012003 and End_Text something similar, and document types 2 & 3 are invoices and credit notes from Cust. Ledger Entry. I’ve also tried: “WHERE (”“Posting Date”” >= {d’" + Range(“Front_Sheet!Start_Text”) + “’})” + _ “AND (”“Posting Date”" <= {d’" + Range(“Front_Sheet!End_Text”) + “’})” + _ on it’s own for the date select, but neither return records. Without any “WHERE” clause the query works ok. Can anyone help on the syntax for this querytext ? Ta, Peter Hodgson
Hi PeterH Your second WHERE clause is the correct one. Just make sure, that the dateformat is yyyy-mm-dd (content of Range(“Front_Sheet!Start_Text”)). If you copy-pasted the lines there is one more problem: There are no spaces before the AND and ORDER BY keywords. This will definately fail the query. You can also add a space at the end of each line. Like this: Wrong: "')" + _ Correct: "') " + _
Thanks for the input, however the date format {d’yyyy-mm-dd’} does not seem to return any records. I have modified the SQL text to this: “WHERE (”“Document Type”" IN (2,3)) " + _ “AND (”“Posting Date”" >= {d’" + Range(“Front_Sheet!Start_Text”) + “’}) " + _ “AND (”“Posting Date”” <= {d’" + Range(“Front_Sheet!End_Text”) + “’}) " + _ The IN clause now works fine, but the date range select does not. I have replaced the {d’” + Range(“Front_Sheet!Start_Text”) + "’} with {d’2003-04-01’} (not using indirect ranges) and still nothing returned. Without the date range but with the IN clause, the query works fine. Therefore, the syntax of the posting date where clauses is not correct, even though this seems to agree with the manual W1CODBC.pdf on the 2.00a CD. Any ideas ? Thanks.
Sorry, but there’s nothing more I can do. It works fine from version 2.60 and up.
So, does this query text work from Excel on your Navision server ? You’ll need to change the connection id DSN from navglobal, and have an Excel sheet with “Global” and “Errorsheet” tabs. It doean’t return anything on mine, and it must be the posting date statements, as removing them returns all records: Sub Test() Msg = “Update data from Global Navision Financials?” Style = vbOKCancel + vbQuestion Title = “Cosmic Consolidated Reporting” response = MsgBox(Msg, Style, Title) If response = vbOK Then Application.Calculation = xlManual SQLConnectionID = SQLOpen(“DSN=navglobal;Option=Integer;Decimal=Decimal”, Range(“ErrorSheet!$D$1”), 2) Range(“ErrorSheet!$D1”).ClearContents Range(“ErrorSheet!$A$1…$C$10”) = SQLError() If Not IsError(Range(“ErrorSheet!$A$1”)) Then Msg = “It was not possible to establish connection. " _ + “The C/ODBC driver returned the following error message: " + Range(“ErrorSheet!$C$1”) Style = vbOKOnly + vbCritical response = MsgBox(Msg, Style, Title) SQLClose connectionNum:=SQLConnectionID Application.Calculation = xlAutomatic Exit Sub End If ’ Global - This part queries the sales data for the period range. SQLExecQuery _ connectionNum:=SQLConnectionID, _ queryText:=“SELECT ““Customer No_””, ““Document No_””, ““Posting Date””, ““Salesperson Code””, ““Amount””, ““Country Code”” " + _ “FROM ““Cust_ Ledger Entry”” " + _ “WHERE (”“Document Type”” = 2) " + _ “AND (”“Posting Date””>={d ‘2003-04-01’} AND ““Posting Date””<={d ‘2003-04-30’}) " + _ “OR (”“Document Type”” = 3) " + _ “AND (”“Posting Date””>={d ‘2003-04-01’} AND ““Posting Date””<={d ‘2003-04-30’}) " + _ "ORDER BY ““Posting Date””, ““Customer No_”” " Range(“ErrorSheet!$A$11…$C$20”) = SQLError() If Not IsError(Range(“ErrorSheet!$A$11”)) Then Msg = "The C/ODBC driver returned the following error message: " + Range(“ErrorSheet!$C$13”) Style = vbOKOnly + vbCritical response = MsgBox(Msg, Style, Title) SQLClose connectionNum:=SQLConnectionID Application.Calculation = xlAutomatic Exit Sub End If ’ Global - This part returns the sales data for the period range. SQLRetrieve _ connectionNum:=SQLConnectionID, _ destinationRef:=Range(“Global!A1”), _ ColNamesLogical:=True Range(“ErrorSheet!$A$21…$C$30”) = SQLError() ’ This is the start of the end !! SQLClose connectionNum:=SQLConnectionID Application.Calculation = xlAutomatic Msg = “Global sales data has been updated” Style = vbInformation response = MsgBox(Msg, Style, Title) Else Msg = “Data was not updated due to an error” Style = vbOKOnly + vbInformation response = MsgBox(Msg, Style, Title) End If ’ Sheets(“Front_Sheet”).Activate End Sub