Reporting Record even if null

[hkimpact]

I have a Form that allows the user to select a date range for an Associate's total Training Hours. After they select the date range the user can select a button on the form that says something like "Get Report". Everything is working as intended. The total training hours is displayed by the Associates name.

But what if I have an Associate that doesn't have a record within that given date range. How can I display those employees on the same report?

Thanks in advance!

 

[vbaInet]

Can you tell us the rationale behind having criteria but still displaying those records that don't meet the criteria?

Perhaps you want to show those employees at the very bottom of your report? If yes, then you need a subreport that will display those employees that don't meet the criteria. The subreport can be placed in the report's Footer section.

 

[hkimpact]

Well here at my job every employee has to have so many hours of training in a year. I want to be able to show all the employees reguardless if they have 0 hours. The reason being, is because an employee MUST have a minimum of lets say 10 hours a year. If I'm looking at the report and see that they have 0 hours in, I will know we need to get on these people for training.

I don't necessarily need subreport at the bottom, but if that's my only option then that's what I'll do.

also I figured I will show you how my tables are set up.

tblAssociate(AssociateID, FName, LName)

tblTraining(TrainingID, TDate, Training, Hours, AssociateID)


Thanks for getting back to me quickly

 

[vbaInet]

So in the SQL statement of your query, you need to change the INNER JOIN to LEFT JOIN or RIGHT JOIN. I can't see the SQL but try out both of them.

The result will be Null and to set Null to 0 you need to use the Nz() function.

 

[hkimpact]

Actually that was what I googled before I came to this Forum. I tried my SQL but I couldn't get to perform the way I wanted to. Here is my SQL.

SELECT tblAssociate.AssociateID, tblAssociate.FName, tblAssociate.LName, tblTraining.TrainingID, tblTraining.TDate, tblTraining.Hours, [LName] & ", " & [FName] AS Name
FROM tblAssociate INNER JOIN tblTraining ON tblAssociate.[AssociateID] = tblTraining.[AssociateID]
WHERE (((tblTraining.TDate) Between [Forms]![AllEmployeeTrainingHours]![txtStart] And [Forms]![AllEmployeeTrainingHours]![txtEnd]))
ORDER BY [LName] & ", " & [FName];

I need to look up the difference between Left and Right join's again. I think I know, but I want to make sure.

 

[vbaInet]

It's just a matter of changing it and viewing the datasheet. If you're unsure, make a copy of your query and try it on the copy.

 

[hkimpact]

I really appreciate all your help vbaInet. I'll keep tinkering around with things until I get this figured out. Have a good day.

 

[vbaInet]

Let us know how you get on.