Counting dates and Null values in Charts

[Caspius]

Hi, I searched the forum for this but the only thread that came close to what I was looking for was this.

http://www.access-programmers.co.uk/forums/showthread.php?t=125240&highlight=null+chart

Basically I have chart in a report thats based on a query that counts the amount of entries per month between two dates inputted by the user.

It all works fine but the chart that is based on the query only shows months that have an entry.

Eg if it counts all dates between the two dates and say the only month that has an entry is July, the chart will only show July. What I want is the other months to show (Null values) as zero, so every month shows. I'm probably missing something basic but can anyone help?

[TextPriDate] is the start date
[TextPriDate2] is the end date

This is the query code (QryDate)

SELECT 
   tblMain.ID1, tblMain.Dt
FROM 
   tblMain
WHERE 
   (((tblMain.Dt) Between [Forms]![frmSwitchboard]![TextPriDate] And [Forms]![frmSwitchboard]![TextPriDate2]));

This the code from the chart in the Report

SELECT 
   (Format([Dt],"MMM 'YY")) AS Expr1, Count(*) AS [Count] 
FROM 
   QryDate 
GROUP BY 
   (Format([Dt],"MMM 'YY")), (Year([Dt])*12+Month([Dt])-1);

Thanks

 

[rfear]

I don't know if this will help but I had a similar scenario in one of my charts recently. The chart displays the total net value of projects as they pass through 5 stages of a process.

I needed to show the entire process / all stages regardless of whether there was a net value.

I set up 2 tables. One with the raw data 'tblData' and what stage the project was at and another with a list of the stages 'tblStages' ( in your case the months of the year I guess ). I also had a sort field in 'tblStages' to get the stages in the right order.

I then set up a query between the 2 tables. Joined the 'stage' field between both and modified the link property so I always showed the stages according to 'tblStages' plus any matching data from 'tblData'.

I hope that makes sense ?

 

[Caspius]

I hope that makes sense ?

Nope. I'm still a bit noob at this so thats probably why!

I'll post a picture of the chart anyway. I've tried a few variations on the coding but I suppose working with null values is like trying to create something out of nothing.

Theres 18 records July which shows fine but the other months there are currently no records, but I want to show them as zero on the chart.