scr
asked on
Creating a parameter (date) query in Excel connecting it to a Sybase Adaptive server 12.5 (ODBC PROBLEM?)
Hi there,
I'm stuck on a little problem.
I'm trying to create some management reports in Excel. After filling a table by executing a stored procedure I want to get a range of values from that table into excel.
I selected all values in Microsoft Query (Select * from F_sel_DossierTurnOver).
Then I added a Criterium, InvDate > [Please enter a date:]
When I enter a value in Microsoft Query there is no problem, until I go to microsoft Excel, it asks to enter a value or enter a cell location for the parameter value.
The following error occurs: [MERANT][ODBC Sybase driver]Inconsistent descriptor information
When I try to change the datatype of the parameter an other error occurs:
Timestamp parameters with zero scale must have a precision of 13, 16, or 19. Parameter number: 7, precision: 2
Can anybody help me? Thanx in advance!
I'm stuck on a little problem.
I'm trying to create some management reports in Excel. After filling a table by executing a stored procedure I want to get a range of values from that table into excel.
I selected all values in Microsoft Query (Select * from F_sel_DossierTurnOver).
Then I added a Criterium, InvDate > [Please enter a date:]
When I enter a value in Microsoft Query there is no problem, until I go to microsoft Excel, it asks to enter a value or enter a cell location for the parameter value.
The following error occurs: [MERANT][ODBC Sybase driver]Inconsistent descriptor information
When I try to change the datatype of the parameter an other error occurs:
Timestamp parameters with zero scale must have a precision of 13, 16, or 19. Parameter number: 7, precision: 2
Can anybody help me? Thanx in advance!
ASKER
Hi,
1> Thats right
2> First, I tried in MS Query to enter a date (like #31/12/2003# (its the dutch form for dates, us style should by like this 12/31/2003)). This works fine. Then I added a parameter, it askes for a value and again, I enter 31/12/2003. This gives the same result. Now, when I return to Excel (leaving the value 31/12/2003 unchanged) I get a error. [MERANT][ODBC Sybase driver]Inconsistent descriptor information
The query change is discarded after pressing ok.
When I try to do this all over but leaving the date parameter empty before returning to Excel, the parameter screen popups asking for a cell. When I enter this cell I get the same error again. [MERANT][ODBC Sybase driver]Inconsistent descriptor information
It does not matter which kind of date I enter in MSQuery. The value in the database is this: 2003-11-26 00:00:00,000. When I enter this value in the parameter in MSQuery, it works fine, but when I enter this value in Excel it generates a problem (as a constant also).
I tried to convert the cells to the right form (=Text(C5;"DD-MM-JJJJ")) and all the other forms but without any result. (J = Y in dutch)
If any of this isn't clear please tell me, I will try to make it clear, I will explain it by images if u want.
Tnx!
1> Thats right
2> First, I tried in MS Query to enter a date (like #31/12/2003# (its the dutch form for dates, us style should by like this 12/31/2003)). This works fine. Then I added a parameter, it askes for a value and again, I enter 31/12/2003. This gives the same result. Now, when I return to Excel (leaving the value 31/12/2003 unchanged) I get a error. [MERANT][ODBC Sybase driver]Inconsistent descriptor information
The query change is discarded after pressing ok.
When I try to do this all over but leaving the date parameter empty before returning to Excel, the parameter screen popups asking for a cell. When I enter this cell I get the same error again. [MERANT][ODBC Sybase driver]Inconsistent descriptor information
It does not matter which kind of date I enter in MSQuery. The value in the database is this: 2003-11-26 00:00:00,000. When I enter this value in the parameter in MSQuery, it works fine, but when I enter this value in Excel it generates a problem (as a constant also).
I tried to convert the cells to the right form (=Text(C5;"DD-MM-JJJJ")) and all the other forms but without any result. (J = Y in dutch)
If any of this isn't clear please tell me, I will try to make it clear, I will explain it by images if u want.
Tnx!
ASKER
I'm thinking about an ODBC driver problem or something like that. Maybe there is a new version available?
Is it possible that there is a problem with the communication between Excel and MSQuery?
I'm running Office XP right now. I tried the same with Office 2003 but with the same results.
Is it possible that there is a problem with the communication between Excel and MSQuery?
I'm running Office XP right now. I tried the same with Office 2003 but with the same results.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi there,
I've created a workaround.
I added an other column in the table with the name SelDate and did the following.
update F_sel_DossierTurnOver
set seldate = convert(varchar,InvDate,11
So I convert the datetime field value to a YYYYMMDD varchar value for selecting date ranges. This works fine now. But it's still strange that the datetime communication between Excel and MSQuery isn't correct.
Thanks for your research and solution.
You get my points for the effort ;)
Greetz ScR
I've created a workaround.
I added an other column in the table with the name SelDate and did the following.
update F_sel_DossierTurnOver
set seldate = convert(varchar,InvDate,11
So I convert the datetime field value to a YYYYMMDD varchar value for selecting date ranges. This works fine now. But it's still strange that the datetime communication between Excel and MSQuery isn't correct.
Thanks for your research and solution.
You get my points for the effort ;)
Greetz ScR
After...... enter a cell location for the parameter value.. when you select the cell with the date in it and press ok.... your error happens, right ?
If you change your query in MSquery, and remove the date as a wildcard and put another criteria there a text column for example, do you still get the same error ?
Just to rule out or comfirm that it's a date format problem.