setting Access query criteria

[Aussie Rules]

Hi,

I have a access 2007 database with a cross tab query.

Based on the selection critieria the cross tab may contain a different
number columns in the result. For example if the query is 'select * from
crosstab where project =1' would retun 6 columns, but if I change the query
to project =2 then the result would be 20 columns.

In my VB2008.net application I create the sql select statement. The problem
seems to be when I do this I get back 20 cols, even if I am after project 1.
the columsn from 7-20 are empty, but they are still there.

When I execute the query within Access 2007, and build the selection
criteria (project =1) into the crosstab query, it works properly and only
returns 6 cols.

So I figure I have to some how set the selection criteria in the cross tab
query in the access database, then use vb to get the results.

How can you in Vb.net, set the Access queries selection criteria and then
execute the query ?

Hope that makes sense...

[Cor Ligthert[MVP]]

Re: setting Access query criteria

This depends completely where you have keep the results of the resultset the
database is returning and how you retrieve that resultset.

You write not one character how you do it in VB. How or what kind of
resultset you get is not important for VB.

Cor

[aaron.kempf@gmail.com]

Re: setting Access query criteria

have you tried doing the same thing with SQL 2005 Express 'PIVOT'
keyword?

I just dont get it; why would someone use Access Database with the
shiny new programming language.
Does VB even support the .ACCDB format?

I think that this shows that MDB format is depecrated.

-Aaron



On Mar 13, 10:38 pm, "Aussie Rules" <aus...@nospam. comwrote:

Hi,
>
I have a access 2007 database with a cross tab query.
>
Based on the selection critieria the cross tab may contain a different
number columns in the result. For example if the query is 'select * from
crosstab where project =1' would retun 6 columns, but if I change the query
to project =2 then the result would be 20 columns.
>
In my VB2008.net application I create the sql select statement. The problem
seems to be when I do this I get back 20 cols, even if I am after project 1.
the columsn from 7-20 are empty, but they are still there.
>
When I execute the query within Access 2007, and build the selection
criteria (project =1) into the crosstab query, it works properly and only
returns 6 cols.
>
So I figure I have to some how set the selection criteria in the cross tab
query in the access database, then use vb to get the results.
>
How can you in Vb.net, set the Access queries selection criteria and then
execute the query ?
>
Hope that makes sense...