Is there a way to ignore Null values in a crosstab query?

[JMalecha]

I've tried Like "*" but get the error message You can't specify criteria on
the same field for which you entered Value in the Crosstab row.

 

[vanderghast]

You can make a first query which will remove rows you don't want and then,
make the crosstab of the previously saved query.

The crossatab may generate a 'cell' with a null in it if there is no record,
in the table/query on which it is based, for the given group (line) and
given pivot-value (column). You can avoid that by changing that null to
something else:. Instead of



TRANSFORM COUNT(*)
SELECT ...


try

TRANSFORM Nz(COUNT(*), 0)
SELECT ...


as example.



Vanderghast, Access MVP

 

[John Spencer MVP]

Try
-- Adding the field again to the list of fields.
Set the Totals Line to WHERE (instead of Group By) and apply the criteria to
this second copy of the field.

If this doesn't work for you post the SQL of the current query, someone can
post a possible solution. And tell us what doesn't work means.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County