Report not pulling values that are blank

[snu]

I have a report that has a query built into it that is supposed to pull the names of two instructors when there are two instructors for a class and it should show one instructor when there is only one instructor. It is only showing the reports for those classes with two instructors, but not those with one instructor.

I have a tblCourseSched table that includes a Fac_ID1 and a Fac_ID2. Fac_ID1 links to tblFaculty in the query and Fac_ID2 links to tblFaculty_1.

This is what I currently have in the query and I know the format is wrong and that is why I am having this problem. The first half of the query I got from someone on here who helped me out. It tells the query what to do if the field is blank. I wanted to make that happen for the instructors from Fac_ID2/tblFaculty_1 as well, but I can't figure out how you format the query for the second instructor. The part in black works fine and will pull classes with just one instructor, but when I try to get the 2nd one to show up it will only pull classes with 2 instructors. It's the part in red that is screwing me up. I can't really post the db bc I've tried "cleaning" it and it doesn't work and there's no way I can do it all manually.

FacName: IIf(Len([tblFaculty].[FacultyFirstName] & "")=0,[tblFaculty].[FacultyLastName],[tblFaculty].[FacultyLastName] & ", " & [tblFaculty].[FacultyFirstName]) & " - " & IIf(Len([tblFaculty_1].[FacultyFirstName] & "")=0,[tblFaculty_1].[FacultyLastName],[tblFaculty_1].[FacultyLastName] & ", " & [tblFaculty_1].[FacultyFirstName])

 

[vbaInet]

Your explanations are a bit fuzzy. Can you tell us what the SQL statement of the query is, a screenshot of what it currently displays and what you you would like it to show.

 

[snu]

Here is the screenshot. I zoomed in on FacName because that is the one that I am trying to get to list both instructors for.

So basically, on the report under FacName I want it to list Faculty_ID1 and Faculty_ID2. The way I have it written now only those classes with 2 faculty members (Faculty_ID1 and Faculty_ID2) show up in my report. I have other classes where there is only one faculty member (Facutly_ID1) and those classes are not populating my reports.

 

[boblarson]

The link to Faculty_ID2 needs to be an OUTER JOIN.

 

[snu]

Didn't even occur to me that it could be something so simple! Thanks again Bob.