complex query fails programatically

[BeemerBiker]

I put together a (what I consider) complex query using the Access wizard. It works fine in access but fails when I code it up. I can actually make it work in code by "simplifyin g" it, but then it does not do exactly what I want it to do.
The following works fine if I click on ! and run it.


Ok - after verifying the above works, I put the part before the "WHERE" into
ads4any.SelectC ommand and put the part after the "WHERE" into ads4any.FilterE xpression
Here is the error


By trial and error I found that if I remove the first two select items, I can get a more meaningful error message:


Qualifying EmplID with "PlannedCourses .EmplID" did not help
Replacing EmplID with "EmployeeID " worked only if I added EmployeeInfo.Em ployeeID to my select statement. However, I do not want the EmployeeID in the final report, only the name. The following image shows that I stepped past the .Select command successfully and indeed, the gridview matched the selected items when I hit the continue key in the VS2008 debug.


Observation: I could not use "EmployeeInfo.E mployeeID" in the FilterExpressio n, but the "EmployeeID " just by itself works fine.

Question: Since the basic query "query1" in first img works fine, can remove the where clause from "query1" and do something like

ads4any.SelectC ommand = "SELECT * FROM Query1()"
ads4any.FilterE xpression = "Category = 'Technical' AND EmplID IN (13288,11506,97 51,13404,12953, 13372,13321,134 29)" ..etc.. since the query never changes, but the filter values do? Maybe the filter will still fail as it does not know what EmplID is and I am not selecting it in the original Query1

best regards

[BeemerBiker]

solved

By trial and error I found that if I remove the first two select items, I can get a more meaningful error message

"Section" seems to be a reserved word, should have been [Section]. That got the better error message without haveing to remove any select items, but the filter expression still did not work unless I included the "EmployeeID " as one of the select items which I didnt want to do.

The following finally worked

ads4any.SelectCommand = sqlcmd + " WHERE " + ads4any.FilterExpression.ToString() ;
ads4any.FilterExpression = "";

The library code handeling the filter mechanism must behave differently than the code that evaluates the entire expression includeing the "where"

In the process of trying to figure this out I ran across a statement that the ms sql server cannot handle " IN (1,2,3, etc) " when the values inside the list are a parameter I was about to switch to a query and a parameter when I read that. I assume that access has the same problem as ms sql server.

[NeoPa]

I'm a bit confused here BB.

Where did MS SQL Server come into the picture? I can see that you're accessing the data from outside of MS Access, but I thought the database itself was processed via MS Access.

It is generally true that SQL run from within Access will use the standard Jet engine, whereas I'm not sure this is always true when ADO or other non-DAO drivers or links are used from other environments.

I don't recall seeing a need for a field to be SELECTED before it can be filtered (WHERE clause) before, although I think I have for a SORT BY clause in Access before. This seems to be the case with your [EmplID] filtering. Quite weird. May be to do with the type of link used. Not sure.