I have made progress in this task but have hit a roadblock that I can't solve. Using a date temp table to create a range of dates and a cross tab query containing an Nz formula, I am able to get the data I want to chart. However, when I attempt to change the chart's rowsource, I get an error that the object can't be updated. Access will create a line chart ( without the zero values I want), but it errors if I try to modify the rowsource that Access automatically generates, specifically when I modify the rowsource with an expression that wraps the value field of the cross tab with "Nz" ( see below in bold print) I even tried using VBA to change the chart rowsource but I have failed. Perhaps this can't be done? I am about to abandon this effort unless I can find a way to modify the rowsource of the Modern chart I have created in a report.
Here is the code to crate the date table:
Code:
CurrentDb.Execute "create table tmptempdatetable (dateID integer NOT NULL, EachMonth datetime )", failonerror
startdate = [Forms]![Print_Client_Reports_Menu]![Text2]
enddate = [Forms]![Print_Client_Reports_Menu]![Text4]
CurDate = startdate
counter = 1
' loops through all the potential dates between start and end date and adds a line to the table
While CurDate <= enddate
CurrentDb.Execute "INSERT INTO tmptempdatetable (dateID, EachMonth) VALUES ('" & counter & "', '" & CurDate & "')"
CurDate = DateAdd("d", 1, CurDate)
counter = counter + 1
Wend
This is query 1 that generates the services provided by month.
Code:
SELECT tmptempdatetable.EachMonth, serviceList.Svc_Name
FROM tmptempdatetable LEFT JOIN (SELECT tblServices.Svc_Name, Instance.InDate FROM tblServices LEFT JOIN (SELECT tblServiceInstance.InDate, tblServicesHistory.Amount, tblServiceInstance.ServiceMonth, tblServicesHistory.Service_ID FROM tblServiceInstance INNER JOIN tblServicesHistory ON tblServiceInstance.Instance_ID = tblServicesHistory.ID) AS Instance ON tblServices.Svc_ID = Instance.Service_ID WHERE ((Not(tblServicesHistory.Service_ID=5)AND Instance.InDate Between #01/01/2020# And #10/08/2021#))) AS serviceList ON tmptempdatetable.EachMonth = serviceList.InDate;
Here is the crosstab sql query that generates the data I want to chart; the bold print is the expression that Access will not allow. Access will not allow the Count([Svc_name]) to be wrapped with Nz. However, If I don't use Nz, I don't get zero values that I need. I also tried an IIF statement but I get the same error.
Code:
TRANSFORM Nz(Count([Svc_Name]),0) AS CountOfSvc_Name
SELECT Format([EachMonth], "mmm 'yy")
FROM Query1
GROUP BY Format([EachMonth], "mmm 'yy"), Year([EachMonth])*12 + Month([EachMonth])-1
ORDER BY Year([EachMonth])*12 + Month([EachMonth])-1
PIVOT [Svc_Name];
Here is the result of that query using some sample data.
Why I can't get a line chart using that data, I do not know. Any help would be appreciated.
Thanks