Print sub reports with No Data

[ozzroo]

How do I print sub reports in my main report that dont have any data. All the fields are default value = 0 but the forms still come up blank.

I want to print the sub reports with the fields showing 0

Any ideas

Thanks

 

[PaulBricker]

You don't give us much information, but the general way to handle something like this is with a join in a query. For example if you have two tables tblEmployee and tblEmpSales. tblEmployee has one field EmpID(PK). tblEmpSales has three fields, EmpID(FK), Sales, Returns. If all your employees have sales and returns, then you will have records for each employee in the corresponding tables. If one of the employees doesn't have any sales or returns yet, there will be a record in tblEmployee, but no corresponding record for them in tblEmpSales. To all employees, you would use a Left Join in a query like this.

SELECT tblEmployee.EmpId, Nz([Sales],0) AS Sales1, Nz(Returns) AS Returns1
FROM tblEmployee LEFT JOIN tblEmpSales ON tblEmployee.EmpID = tblEmpSales.EmpID;

This will give you a record for each employee with 0's in the Sales and Return records for any employee that doesn't have them yet. Then if you used this data in a Report, you will have something to show.

Post back with specific questions.

Paul

 

[ozzroo]

Tried but cant get it working

My code for query is:

SELECT tblIncome.IncomeID, tblIncome.ClientID, tblIncome.BasicSalary, tblIncome.Commission, tblIncome.Bonus, tblIncome.Dividends, tblIncome.Drawings, tblIncome.TotalMonthlyIncome, tblIncome.TotalMonthlyExpenditure, tblIncome.TaxBand
FROM tblIncome INNER JOIN tblClients ON tblIncome.ClientID = tblClients.ClientID;

 

[PaulBricker]

Troubleshooting SQL is not my strongest point, but try this. Note the changes I made in bold You want the ClientID from your Client table to show all ClientID's, not from your Income table.

SELECT tblIncome.IncomeID, [b]tblClients.ClientID[/b], tblIncome.BasicSalary, tblIncome.Commission, tblIncome.Bonus, tblIncome.Dividends, tblIncome.Drawings, tblIncome.TotalMonthlyIncome, tblIncome.TotalMonthlyExpenditure, tblIncome.TaxBand
FROM tblIncome [b]LEFT JOIN[/b] tblClients ON tblIncome.ClientID = tblClients.ClientID;

Paul