columns missing in crosstab

L

ljbartel

I have a crosstab query that generates 5 columns when run within Access
2000. However when I run the same sql with ADO in a VB project it only
returns 3 columns. Has anyone seen this problem? It appears to be
dropping the columns that contain some null values when in VB. Any
ideas on how to fix this?

thanks
LJB
 
D

David Lloyd

I am not sure if this addresses your issue, however, when constructing a
crosstab query columns containing null values are excluded from the result
set unless you specify that you want the columns that you want to appear in
the result set.

In Access, you specify the columns to appear through the ColumnHeadings
property of the crosstab query. (Right click in the query designer and
select Properties to show the query properties.)

In an SQL statement, use must use the "Pivot In" syntax. For example:

PIVOT MyField IN ("Value1", "Value2", ...)

In this way, the columns specified always appear in the results even if they
contain null values. See the following additional reference:

http://msdn.microsoft.com/library/d...ry/en-us/vbaac10/html/acproColumnHeadings.asp

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a crosstab query that generates 5 columns when run within Access
2000. However when I run the same sql with ADO in a VB project it only
returns 3 columns. Has anyone seen this problem? It appears to be
dropping the columns that contain some null values when in VB. Any
ideas on how to fix this?

thanks
LJB
 
L

LJB

I immediately thought you were on the right track but it was not to be.
It turns out ADO and Visual Basic do not like SQL "LIKE". It works in
Access but fails silently in ADO. Someone please tell me where that's
documented!

thanks
LJB
 
L

LJB

Please ignore my previous post. It contains misinformation. The answer
is I needed SQL wild cards (% and _) to make LIKE work through ADO.
I suspect you've run into a "wildcard" issue. Queries run inside Access
require the Jet wildcards (* and ?) to be used as wildcards in LIKE
comparisons. ADO requires the use of ODBC wildcards (% and _), regardless of
the backend database being used (the Jet OLE DB provider translates them
into the Jet wildcards). It is counterintuitive, but even saved queries are
subject to this rule. Saved queries run by ADO must contain the ODBC
wildcards, not the Jet wildcards.

Bob Barrows
Click to expand...

LJB
 
Top