Access Chart error 1

[balllian]

i have a developed a chart within access to show records for each month throughout the year, When no records have been created for a month this doesnt appear as zero but a blank field. Is there a way of populating a blank field with a zero

thanks in advance

Ian

 

[dhookom]

Could you share the SQL view of your chart's Row Source 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]

 

[balllian]

here u go

TRANSFORM Count(QFOrPivot_table_2005.[Previous No/Ref]) AS [CountOfPrevious No/Ref]
SELECT (Format([Date of Discovery],"mmm"" '""yy")) AS Expr1
FROM QFOrPivot_table_2005
GROUP BY (Year([Date of Discovery])*12+Month([Date of Discovery])-1), (Format([Date of Discovery],"mmm"" '""yy"))
PIVOT QFOrPivot_table_2005.[Area of Source of Breach].[Area Source of Breach];

 

[dhookom]

You would need a table that has all possible months in your range. You can use a left or right join in a query to display all months as row headings.

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]

 

[balllian]

if i was to create a make table from my query and recreate the chart from this new table and the month had no records to record. how would it be possible to change the null field in the table to a '0' value

 

[dhookom]

I'm not sure why you would "create a make table from my query". I think you just need a table of all possible row heading values that can be joined to the results of your crosstab. You can use Nz() to display 0 rather than Null.

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]

 

[balllian]

where would i need to put the NZ() in my row sourse property?

 

[dhookom]

TRANSFORM Val(Nz(Count([Previous No/Ref]),0)) AS [CountOfPrevious No/Ref]

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]