How do I get the Navision driver to accept dates in the SQL WHERE… clause? The VBA statement: Query = “select ““Document Type””, ““No_””, " & _ “”“Document Date””" & _ " from ““Sales Header””" & _ " where ““Document Date”” = ‘" & DateFrom & "’" & _ " order by ““Document Date””" where DateFrom has a value of ‘010101’ gives me an error of S0000 1023 [Navision Software a/s][Navision Financials ODBC Driver]Invalid operand for operator: = What date format is the driver actually after?
The correct syntax for using date ranges with C/ODBC is a little strange, but once you have the specifics, you can do the same kind of date filtering that exists in C/SIDE. Like this: where date_in_question >= {d’2000-02-28’} This sample is from an ASP page we are running. I believe that it’s important to use four digit year values, and two digit day and month values. Make sure you set your date variables up accordingly. Like This: sql = "select Sales_Invoice_Header.Order_No_, " _ & "Sales_Invoice_Header.Sell_to_Customer_No_, " _ & "Sales_Invoice_Header.Name, " _ & "Sales_Invoice_Header.Posting_Date, " _ & "Sales_Invoice_Header.Amount_Including_Tax, " _ & "Sales_Invoice_Header.Inhouse_Order " _ & "from Sales_Invoice_Header " _ & “where Sales_Invoice_Header.Sell_to_Customer_No_ = '” & CustRS(“No_”) & "’ " _ & “and Sales_Invoice_Header.Posting_Date >= {d’” & BackYYYY & “-” & BackMM & “-” & TodayDD & "’} " _ & “and Sales_Invoice_Header.Posting_Date <= {d’” & TodayYYYY & “-” & TodayMM & “-” & TodayDD & "’} " Hope this helps! Jack Gentle MIS Manager DC Shoes, Inc. jgentle@dcshoes.com
Thanks for that - have not got it working from Excel 2000 Excellent! Code now looks like@ tDate = Format(Worksheets(“data”).Range(“b1”), “dd/mm/yyyy”) dateYYYY = Mid(tDate, 7) dateMM = Mid(tDate, 4, 2) dateDD = Left(tDate, 2) ’ Required date format: {d’yyyy-mm-dd’} qryText = “select ““Document Type””, ““No_””, " & _ “”“Document Date””, ““Salesperson Code””" & _ " from ““Sales Header””" & _ " where ““Document Date”” >= {d’" & dateYYYY & “-” & dateMM & “-” & dateDD & “’} " & _ " order by ““Document Date””” sqlexecquery _ connectionnum:=SqlConnectionID, _ querytext:=qText
But how can i insert data into a table? What is the correct syntax for a date value if i use the SQL command: insert into … values ( … ) {d’2001-03-20’} doesn’t work. Markus
I am having trouble trying to define a date range. I can have all records after a specified date or before it but I cannot get it to filter records between two dates, for example, 010102…310102 The statement is as follows: qryText = “SELECT ““Key 1"”, ““Date”” FROM ““Journal”” WHERE ““Key 1"” BETWEEN '” + Range(“Ratios!$B$4”) + “’ AND '” + Range(“Ratios!$B$5”) + “’ " + _ “AND (”“Date”” <= {d’2000-10-31’})” + _ “AND (”“Date”” >= {d’2000-10-10’})" + _ “AND (”“Table No_”" = 66027)" + _ “AND (”“Field No_”" = 3)" + _ “AND (”“Old Value”" = ‘Quote’)" + _ “AND (”“New Value”" = ‘In Progress’)" + _ “AND (”“Type”" = ‘Modify’)" The hardcoded dates are a temporary measure until I can get the statement working correctly. I don’t get any errors, just no records but I know that there are some because I have looked in the table. How can I define a date range?
Hi I’m having a problem which I think is related to this. When I am retrieving data using ODBC into Excel, I want to let the user define the Posting date range (setting the filter) directly when updating the Worksheet. This can be done in any field in the MS Query, but I can’t make it work with the date-fields. Now the question: Does anybody know the correct syntax for this? Thanks Best Regards Mads Morre DK
I have used (“Posting Date”={ts ‘2001-01-03 00:00:00’}) for successfully retrieving dates through ODBC. Sometimes, Transactional SQL (ts) seems to be working better. Building the right query can be complicated, but using Excel Get External Data can be quite helpful. When you’ve got data in successfully, look at the SQL Query generated and copy over whatever part you need. John
Hi John I can’t make this work. I will try to explain further, what i’m doing. In MS Query under “Criteria” (On ex. Posting Date) I write [Date] and then the User is asked for a date when He/she sends the data back to Excel. Then I get the message (Conversion Error). Thanks Best Regards Mads Morre DK
Hi John I can’t make this work. I will try to explain further, what i’m doing. In MS Query under “Criteria” (On ex. Posting Date) I write [Date] and then the User is asked for a date when He/she sends the data back to Excel. Then I get the message (Conversion Error). Thanks Best Regards Mads Morre DK
You have to enter the date in the yyyy-mm-dd format. That works fine here with C/ODBC from Attain 3.01B. As example I retrieve entries from the Cust. Ledger Entry table, using the following query: SELECT “Cust_ Ledger Entry”.“Customer No_”, “Cust_ Ledger Entry”.“Posting Date”, “Cust_ Ledger Entry”.“Sales (LCY)” FROM “Cust_ Ledger Entry” “Cust_ Ledger Entry” WHERE (“Cust_ Ledger Entry”.“Posting Date”=?) John
Hi John, Does exactly the same thing work if trying to enter a date range ? Can you give an example of using a date range ? Many Thanks
Hi, The ODBC specification defines 3 canonical forms for dealing with date literals, when not using ? placeholders to represent values. From the SDK reference: =============== The escape sequence for date, time, and timestamp literals is {literal-type ‘value’} where literal-type is one of the values listed in the following table. d Date yyyy-mm-dd t Time hh:mm:ss ts Timestamp yyyy-mm-dd hh:mm:ss[.f…] =============== All of these should(!) work for the C/ODBC driver. Note that they represent ONE value. Ranges are represented with TWO separate values and a relational operator, or BETWEEN, in the normal SQL way, like: … WHERE MyDate >= {d ‘2002-04-16’} AND MyDate <= {d ‘2002-10-17’} or … WHERE MyDate BETWEEN {d ‘2002-04-16’} AND {d ‘2002-10-17’} I am not sure if C/ODBC supports the BETWEEN syntax, though. They should also work for the SET of an UPDATE statement, and insertion values in the VALUES of an INSERT statement.
Hi, It supports the BETWEEN syntax, however, when I tried to use it on a Date field, it did not work. I then tried the ‘>= and <=’ syntax, this did not work either. When I entered: date >= {d’yyyy-mm-dd’}, it worked fine, but it does not seem to accept a range. I can define a date range on a Date Filter field but not a Date field. Can anyone help? Debbie Edited by - Debbie Wood on 2002 Feb 28 12:23:00
Anybody out there use anything besides msQuery/Excel/Access with C/ODBC ?? I use a lot of Perl routines to access my Navision data, and the dates in C/ODBC stopped working after version 2.10. (versions 2.60b - 2.60f all have the same problem). I will craft an SQL statement (which worked with 2.10) that has a " WHERE (Posting_Date >= {d’2002-01-01’} AND Posting_Date < {d’2002-02-01’}) ". The query works if you run it through msQuery, but if you try to go through Perl, or anything else that doesn’t use MS dlls, it returns 0 records. I have no idea why… you’d think anything that uses the ODBC driver wouldn’t be discrimated, but not so. Interesting to note that the query takes the same amount of time to run, just doesn’t return any values. Has anybody had this same problem? And moreso, anybody know a solution? Aston group support has stopped responding to my emails, even though we pay them yearly, I can only assume because they are as stumped as I am.