By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Convert null to 0 in crosstab query...

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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!
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.