Report won't open when fields empty

[Niniel]

Hello,

In my database I had two tables that contained names which I used to look up
and copy into the main table. There was no relationship between the two
tables and the main table.
Now I changed the setup so that the main table only stores a references to
the name tables, ie. the IDs.
This works well for the forms but is causing problems in the reports. When
either one or both of the name fields were left empty, the report won't open
because the underlying query is not showing the record; but when there's data
in the record, it works fine.

Does anybody know a way around this dilemma?
Thank you.

Below is the SQL code. The problem is caused by PrimaryStaff and Coordinator.

SELECT tblActivity.ActivityID, tblActivity.Title, tblActivity.NewCourse,
tblActivity.CIK, tblActivity.OldCIK, tblActivity.BeginDate,
tblActivity.EndDate, tblActivity.CostCenterNr, tblActivity.SAP,
tblActivity.LocationID, tblLocation.Location, tblLocation.Address,
tblLocation.City, tblLocation.State, tblLocation.Zip, tblLocation.Phone,
tblLocation.Fax, tblLocation.[E-Mail], tblLocation.Web,
tblActivity.CourseDirectorID, tblCourseDirector.DirectorName,
tblCourseDirector.SSN, tblCourseDirector.SponsoringDept,
tblCourseDirector.Address, tblCourseDirector.City, tblCourseDirector.State,
tblCourseDirector.Zip, tblCourseDirector.Phone, tblCourseDirector.Fax,
tblCourseDirector.[E-Mail], tblActivity.SubmittedBy, tblActivity.Description,
tblActivity.Objectives, tblActivity.MailingListSources,
tblActivity.OtherActivities, tblActivity.CancelationPolicy,
tblActivity.DateSubmitted, tblActivity.ApprovedBy, tblActivity.DateApproved,
tblActivity.CCEReport, tblActivity.WebPublish, tblActivity.Intranet,
tblActivity.CoordinatorID, tblActivity.PrimaryStaffID,
tblCoordinators.CoordinatorName, tblPrimaryStaff.PrimaryStaffName
FROM tblPrimaryStaff INNER JOIN (tblLocation INNER JOIN (tblCourseDirector
INNER JOIN (tblCoordinators INNER JOIN tblActivity ON
tblCoordinators.CoordinatorID = tblActivity.CoordinatorID) ON
tblCourseDirector.CourseDirectorID = tblActivity.CourseDirectorID) ON
tblLocation.LocationID = tblActivity.LocationID) ON
tblPrimaryStaff.PrimaryStaffID = tblActivity.PrimaryStaffID
WHERE
(((tblActivity.ActivityID)=[Forms]![frmBrowseApplications].[ActivityID]))
ORDER BY tblActivity.ActivityID;

 

[Niniel]

I found a solution - had to change the join type for these two relationships so that even records are shown that don't have a corresponding value in those two tables.

Pretty simply solution, now that I know it.