CROSS TABS: Forcing Zeros in Blank Cells

[LarryDeLaruelle]

I use cross tab queries extensively to set up data that I then copy/paste into Excel to produce charts and graphs.<br><br>It is a minor irritant, but still really irks me, that I must insert a zero in each copied cell that does not contain a value.&nbsp;&nbsp;The Excel chart builder does strange things with the series ranges if any cells are blank.<br><br>Is there a way to force Access to zero-fill a crosstab when no value is returned?&nbsp;&nbsp;I've tried changing the format of the count/value column (General, Fixed, Standard) to no avail.&nbsp;&nbsp;I've also tried the Nz function with the count/value column but that didn't work either.<br><br>First question:&nbsp;&nbsp;Is it possible to force a Zero-Fill?<br><br>Second:&nbsp;&nbsp;How do you do that?<br><br>Thanks very much. <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>

 

[DougP]

Not inside the SQL I don't think.<br>Are you creating a Table with the results that you pass to Excel?<br>You could create a function that looked at the values in the table and put a zero in there.<br>And probably put the SQL code from your query in a VBA Execute statement. like<br><br>db.Execute &quot;SELECT ......<br><br>and do the whole thing with one click of a button including launching Excel and running some VBA over there too.<br><br>Or can't you do something similar on the Excel side?<br>=IIF(IsNull(Cell),0,Cell)<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[LarryDeLaruelle]

Thanks for the reply Doug.<br><br>I have a series of charts and graphs I produce using Excel with the data coming from Access Crosstabs.<br><br>What I am currently doing is running the CT Query, copying the result and pasting it into an Excel spreadsheet.&nbsp;&nbsp;I then select another field for the Row Heading in the CT Query and repeat the copy/paste.&nbsp;&nbsp;<br><br>On some report sets, I may do this flip-flop a half a dozen times; for others up to 20.<br><br>I would like to bring more automation to this, but I'm not sure my SQL coding skills are up to the challenge (also, I'm not sure just how to get started with it).&nbsp;&nbsp;I would gladly accept any suggestions or helpful tips you could provide.<br><br>I tried your =IIF formula in Excel (I had to convert it to =if(isblank(b2),0,b2)).&nbsp;&nbsp;When I put that in a cell it works fine but when I drag/copy it to cover the range of cells I still get blanks.<br><br>Thanks again. <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>

 

[Ymesei]

To get the query to return a 0 for empty fields, just wrap the field or formula for your crosstab value with the following:<br><br>Nz([Field Name],0)<br><br>Of course, replace Field Name with the field the value is in or the formula used to get the value.<br><br>Here's an example that works for me:<br><br>TRANSFORM Nz((Sum(qMarketAgency1.Pax)),0) AS [The Value]<br>SELECT qMarketAgency1.Market, qMarketAgency1.Agency<br>FROM qMarketAgency1<br>GROUP BY qMarketAgency1.Market, qMarketAgency1.Agency<br>PIVOT qMarketAgency1.Option;<br><br>It returns the total number of Pax from a table qMarketAgency1 with row headings from the fields Market and Agency and column headings from the field Option.<br><br>Hope this makes sense to you.

 

[LarryDeLaruelle]

Thanks Ymesei.<br><br>I had tried the Nz function earlier but with no result.&nbsp;&nbsp;I thought your syntax of indicating a fill value of zero would work but no joy.<br><br>When I have some time, I will try to set up the sql code you suggested to see if that will work.<br><br>Thanks again. <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>