How do I include zero values on a column graph by period?

[StuKregor]

I am creating a column graph to show number of transactions per week/month/quarter over a period. There are times however when there are no transactions in a week/month/quarter, and currently my graph doesn't show these zeroes (because my query doesn't show it). Do I need to change my query or is there a way to force the graph to include the dates that are missing from the data?

Assuming it's the query how can I create a record for each period, even when there are no transactions in that period?

The code I use to generate the period that I then group on is:

Public Function ByPeriod(DateRaised As Date) As String

Dim vPeriod As Integer

veriod = Forms![SelectPeriod]![optPeriod]

If vPeriod = 1 Then
ByPeriod = Format(DateRaised, "ww"" '""yy&quot
ElseIf vPeriod = 2 Then
ByPeriod = Format(DateRaised, "mmm"" '""yy&quot
ElseIf vPeriod = 3 Then
ByPeriod = Format(DateRaised, "q"" Qtr - ""yy&quot
Else
ByPeriod = DateRaised
End If

End Function

 

[tlbroadbent]

One way to do this is to create a master calendar table containing all of the dates you might want to select. The create a join query as the source for the graph. It would look something like the following. Note the Left Join. That is critical.

Select ByPeriod(Master.DateRaised) As Period, sum(nz(DataTbl.TranCnt,0)) As TotTrans
From Master Left Join DataTbl
On Master.DateRaised = DataTbl.DateRaised
Where DateRaised Between #1/1/2001# And Date()
Group By ByPeriod(Master.DateRaised)

I've made a few guesses so you will likely need to adjust. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin