Convert null to 0 in crosstab query...

[Matthew Wells]

I have a crosstab query based on anothe query. The base query resultset has
no null values in its "Quantity" column. However, when I create the new
crosstab query from the base query, the records that should be 0 are blank.
I trid the old IIF(IsNull(....etc but that didn't help. How do I make my
blanks 0s?

Thanks.

Matthew Wells
MW****@NumberCruncher.com

[Rich P]

This is just a workaround. But what you do is to convert your
underlying query (your base query) into a static table (a regular table)
like use a MakeTable query or you could manually create your table with
the same structure as your underlying query and use an Append Query Or
use VBA to populate it with

DoCmd.RunSql "Insert Into tblNew(fld1, fl2,...fldn) Select t1.fld1,
t1.fld2,...t2.fldn from tbl1 t1 Inner Join tbl2 t2 On t1.ID = t2.ID"

If you have 0's in your table, then those 0's will show up in the
crosstab query. I have had problems basing a crosstab query on an
underlying query. The fix that has worked for me is to base all
crosstab queries on regular (static) tables.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!