"where" clause works where FilterExpression fails.

[Beemer Biker]

I got my cross tab query to work by doing the following just before
executing select

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

One would think the above should not have been necessary if the MS handler
for the FIlterExpression was the same MS code handler as the one that
handled the clause after the " WHERE ".

I assume the problem is related to the filter expression haveing "EmployeeID
IN (1,2,3 etc) " but I DID NOT have "EmployeeID" in the select items as I
didnt want it to show up in the data grid.

I found that if I added EmployeeID to the selected items, then my original
query worked fine as-is. The query had two inner joins and I assume that
something got broken when the handler processeS the FilterExpression
separate from the select statement.



According to http://tinyurl.com/b2bspt ms sql server cannot use a
parameterized comma delimited list. Is this also true for access? I was
tempted to use parameters in the above FilterExpression, but since I did get
it working using that " WHERE " I really dont want to change my code.


best regards

 

[huangda ??????]

I got my cross tab query to work by doing the following just before
executing select

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

One would think the above should not have been necessary if the MS handler
for the FIlterExpression was the same MS code handler as the one that
handled the clause after the " WHERE ".

I assume the problem is related to the filter expression haveing
"EmployeeID IN (1,2,3 etc) " but I DID NOT have "EmployeeID" in the select
items as I didnt want it to show up in the data grid.

I found that if I added EmployeeID to the selected items, then my original
query worked fine as-is. The query had two inner joins and I assume that
something got broken when the handler processeS the FilterExpression
separate from the select statement.



According to http://tinyurl.com/b2bspt ms sql server cannot use a
parameterized comma delimited list. Is this also true for access? I was
tempted to use parameters in the above FilterExpression, but since I did
get it working using that " WHERE " I really dont want to change my code.


best regards

 

[david]

You can't filter on a field that does not exist in the recordset.

This is a difference between a filter applied to a recordset,
and a WHERE clause applied to the source tables.

When you apply a WHERE clause to the source tables, JET
and SQL Server are able to understand that you need to see
that field so that you can filter on it.

When you apply a filter to a recordset, it is too late: either the
data is there to be filtered or it isn't.

Some older database engines work like this too: they won't
let you apply a WHERE clause to data that hasn't been selected.
But Jet and SQL Server have an intelligent query optimising
process that selects the fields you need for joins and WHERE
clauses even if you don't have them explicitly listed in the
SELECT statement.

(david)