Hi Everyone
In the attached database the Report displays the Sum of All Data in the Current Month.
If No Data has been entered for any Previous Months is it possible to Display a Zero Value?
What I want to see in the Report is this:-
Hi Everyone
In the attached database the Report displays the Sum of All Data in the Current Month.
If No Data has been entered for any Previous Months is it possible to Display a Zero Value?
What I want to see in the Report is this:-
make a 'report' table to hold all months with zeros.
empty, then add the months & zeros of your date range, every time you begin the report. Q1 will pull this data.
then Q2 is your regular data from the main table.
Q3 is a union query that adds both Q1 & Q2.
run the report on Q3.
Hi ranman
This Report is just a subreport that is giving a Summary of data for the Main Report.
It is not based on Quarters at all.
what is the controlsource to your Current Month? It may be you can use the format property to display 0 if null
Hi Ajax It it using the Date fields in the underlying query
Sorry - I meant previous month and I mean what you actually have in the controlsource - =DSum(whatever), not a description
Hi Ajax
I attache the modified example with some fields removed from the table.
At the moment the query lists all of the records for this Month which are then displayed in the Report as the Current Sum of Additions
What I am trying to do is add to the query a criteria that would show a Zero Value for the Previous Months, even though there are no previous records.
Hope that makes sense?
so basically you want a single query that provides a brought forward figure.
You can't do that if the data doesn't exist, so you have to create it
use something like this as the control source to your previous month control
or drop the nz and use the format property as previously suggested to display 0.00 when nullCode:=Nz(DSum("additions","tblChangeOrderSummary","Periodto<=dateadd('d',-day(date()),date())"),0)
You can apply the same principle in an aggregate query by using a calculated date
however that will not produce a zero previous balance if there are no recordsCode:SELECT iif(Periodto<=dateadd('d',-day(date()),date()),dateadd('d',-day(date()),date()),PeriodTo) as Period, sum(additions) as addition FROM tblChangeOrderSummary GROUP BY iif(Periodto<=dateadd('d',-day(date()),date()),dateadd('d',-day(date()),date())
An example file added.
I added a table where months are registered as 1st of month (you can register months for any time period for advance there), and in your table instead of separate fields for incoming and outgoing sums, added a single sums field and a sign field. I left your old sum fields in place (so your original query still works), but they are now abundant.
Hi ArviLaanemets
I am sorry but I am not following your solution.
How would I populate the table's Sign field from the Data Input Form?