Attain vs MS Access via ODBC: Date fields??

Business Central/NAV Developers Forum
1

Hi, I’m having problems in retrieving data from Attain to MS Access via ODBC when I put some filtering criteria on any date field. The ODBC driver simply doesn’t understand the date filter & returns an error. On the other hand, it works fine with MS Excel. Any one have any ideas, WHY & HOW can I fix this problem?

2

Can you tell for more explicit way, how do you use the ODBC? via code or simply links navision tables via ODBC? Anu

3

There is a thread named ‘C/ODBC and dates’. That might be helpful. Anu

4

Hi Anu, I’m using ODBC (ver. 3.10) “simply” by linking Financils’ tables in MS Access. If you don’t try to filter on a date field then it extracts all records from Financials but replaces “Null” with “01-01-1753” & if you try to retrieve records for a specific date then gives the following error, “ODBC: The called fuction failed. [Navision Software a/s][Navision Attain ODBC Driver]Invalid operand for operator:=(#1023)”. I’ve developed tens of MS Access databases basing on Financials & the only way around is to import all of the records in a table firrst & then filter them by date, but you can imagine how long it takes to import 1000s of records! Tariq Edited by - tariq on 2002 Mar 05 11:17:47

5

I know that this was an “issue” with C/ODBC ver 2.60. Apparently it’s still a problem in 3.x. :frowning: We ended up having to create fields in Navision that would store a value based on a date filter and then let ODBC pull that value instead of the flow field value. Wasn’t pretty, but for our particular situation (external system reading Navi data at a specific time every day), it worked OK.

6

See C/ODBC guide. you can use following sql (in MSA better to use ‘Pass-through’ query) SELECT * FROM Some_Navision_Table WHERE Date_Field = {d ‘2002-01-17’} ^^^^^^^^^^^^^^^^ IN THIS FORMAT ONLY - ‘YYYY-MM-DD’! Business Applications Programmer Sertified Navision Developer SIA “Sintegra” Latvia

7

Previous topic was only for normal Date fields. But for date-filters you must use another: WHERE Date_Filter = ‘…311201’ Business Applications Programmer Sertified Navision Developer SIA “Sintegra” Latvia

8

Thanks everybody, I solved the problem by uncheking “Commit Cache” and “Closing Date Support” in C/ODBC options. “Identifiers” is still set to “All Except DOT”, though Hein suggested to change it to “All Except Spaces” Bye Tariq