How to print zeroes

[tar28un]

hi there,

I am creating a cross tab query which is getting data from two tables . I am calculating count in my value field. Now everything is working fine except the fact that the query is not showing me the fields where the count is zero. I have used nz for that. Is there is any other way I can do that. My sql statement looks like this:

TRANSFORM nz(Count(Master_School_List.DfES),0) AS CountOfDfES
SELECT [School Types].School
FROM Master_School_List INNER JOIN [School Types] ON Master_School_List.Type = [School Types].Type
WHERE ((([School Types].Type)=0))
GROUP BY [School Types].ID, [School Types].School
ORDER BY [School Types].ID
PIVOT Master_School_List.District;

 

[dhookom]

Using Nz() is the solution that I use except that I also add Val() to explicitly make the result numeric:

TRANSFORM Val(nz(Count(Master_School_List.DfES),0)) AS CountOfDfES
SELECT [School Types].School
FROM Master_School_List INNER JOIN [School Types] ON Master_School_List.Type = [School Types].Type
WHERE ((([School Types].Type)=0))
GROUP BY [School Types].ID, [School Types].School
ORDER BY [School Types].ID
PIVOT Master_School_List.District;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tar28un]

Hi there,

Many thanks for the reply. I tried this option as well but it didnot work. What actually happening is that when I take out the condition "WHERE ((([School Types].Type)=0))" then it works and it show Zeroes in all blank coloumns . Its only when I put this condition to show only the result of one type then it is causing an error.I want to code the show result for all districts whether or not there is a value in it. But for that specific type there is value only in 3 districts, so it is displaying only 3 coloumn, And I have to base my report on this and my other subreports would have value in the other fields as well so this will cause a chaos.

 

[dhookom]

So you were missing columns or rows rather than values. If you are missing columns then you need to set the Column Headings property to all possible districts.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tar28un]

Hi there,

Thanks for the reply. Can you tell me how can I set this property as I am making a crosstab query.

 

[dhookom]

Display the properties dialog of the crosstab when in design view and enter each unique District value in the Column Headings property.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tar28un]

Hi there,

Many thanks. It worked. I actually found it before you reply from one of you previous posts.

Regards,

Sarika