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 "simplifying" 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.SelectCommand and put the part after the "WHERE" into ads4any.FilterExpression
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.EmployeeID 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.EmployeeID" in the FilterExpression, 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.SelectCommand = "SELECT * FROM Query1()"
ads4any.FilterExpression = "Category = 'Technical' AND EmplID IN (13288,11506,9751,13404,12953,13372,13321,13429)" ..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