Report based off query shows no results if certain fields are blank

[cvandijk]

Good morning all,

I am coming to the community with a question in regards to my report which is based off a query. Let me post what my query looks like and what I have for the launch code on the form:

Query:

SELECT tblProbateInfo.IndexNo, tblProbateInfo.DateBondFiled, tblAttorney.FirstName, tblAttorney.MiddleInitial, tblAttorney.LastName, tblAttorney.Suffix, tblAttorney.Address1, tblAttorney.Address2, tblAttorney.City, tblAttorney.PhoneNumber, tblProbateInfo.DateOfDeath, tblProbateInfo.EstateYear, tblProbateInfo.Estate, tblProbateInfo.EstateFirstName, tblProbateInfo.EstateMiddleInit, tblProbateInfo.EstateLastName, tblProbateInfo.EstateSuffix, tblProbateInfo.AdvertisingFee, tblProbateInfo.EstateFee
FROM tblProbateInfo INNER JOIN (tblAttorney INNER JOIN tblAttorneyCase ON tblAttorney.AttorneyID=tblAttorneyCase.AttorneyID) ON tblProbateInfo.IndexNo=tblAttorneyCase.IndexNo;

Code in a button on the form:

Private Sub cmdCardView_Click()
Dim strWhere As String
Dim strReportName As String

strReportName = "rptCardView"
strWhere = "[IndexNo]= " & Forms!frmProbate!IndexNo

DoCmd.OpenReport strReportName, acViewReport, , strWhere

End Sub

Certain estates do not have any attorney related to them and when I run the card view button for those estates, the report is entirely blank. However, should I choose card view for an estate that does in fact have an attorney, the card fills in all of the proper information. How do I get this report to fill in all the fields that have data regardless of whether certain fields are populated or not?
I am probably overlooking something very simple. I have tried searching the site for what I think my problem was but it wasn't helping at all.

Any help on this issue would be greatly appreciated.

 

[lespaul]

you need to change the INNER JOIN into the AttorneyCase Table to a LEFT OUTER JOIN...but I'm not sure what the Access parens are going to have to be...this should work:

SELECT P.IndexNo, P.DateBondFiled, A.FirstName, A.MiddleInitial, A.LastName, A.Suffix, A.Address1, A.Address2, A.City, A.PhoneNumber, P.DateOfDeath, P.EstateYear, P.Estate, P.EstateFirstName, P.EstateMiddleInit, P.EstateLastName, P.EstateSuffix, P.AdvertisingFee, P.EstateFee
FROM tblProbateInfo P
LEFT OUTER JOIN  tblAttorneyCase C ON P.IndexNo = C.IndexNo 
INNER JOIN tblAttorney A ON C.AttorneyID = A.AttorneyID

HTH

leslie

 

[cvandijk]

Leslie that did just the trick. Your post prompted me to start messing with the join properties of the query and I got just what I was looking for. I haven't really worked seriously with joins for a considerable number of years so this slipped my mind.

Thanks a ton for all your help. I should be able to get this project close to completion now.

 

[lespaul]

awesome! for future reference you may want to read this Understanding SQL Joins - really makes it clear what the differences are and how to use the correct type.

Leslie