My report is not showing all fields required from the source query. (1 Viewer)

atrium

Registered User.
Local time
Today, 11:04
Joined
May 13, 2014
Messages
348
when I view the source query

SELECT Clients.ClientId, Clients.ClientGroup, Clients.ClientFileNumber, RTrim([Clients]![FirstName] & " " & [Clients]![LastName]) AS ClientName, Clients.LastName, DdSchedules.Status, Matters.Description, Matters.DebtPrinciple, IIf([JudgementAmount]>0,[JudgementAmount],[DebtPrinciple]) AS MainDebt, IIf([JudgementAmount]>0,"J/Debt","P/Debt") AS PrincOrJudgFld, Matters.JudgementAmount, SumOfInterestQry.TotInterest, Matters.InterestAmount, SumOfPaymentsMadeQry.TotPayments, IIf(([MainDebt]+Nz([InterestAmount]))-Nz([TotPayments])<>0,([MainDebt]+Nz([InterestAmount]))-Nz([TotPayments]),0) AS Balance, Matters.ClientRefNo, Matters.OperStatus, Matters.MatterId, Matters.MatterShortNo, Matters.MatterLongNo, Matters.ClientId, Matters.MatterTitle, Matters.Description, Matters.CollLongNo, Matters.CollShortNo, RTrim([Debtors]![FirstName] & " " & [Debtors]![LastName]) AS DebtorsName
FROM ((((Clients LEFT JOIN Matters ON Clients.ClientId = Matters.ClientId) LEFT JOIN Debtors ON Matters.MatterId = Debtors.MatterId) LEFT JOIN SumOfInterestQry ON Matters.MatterId = SumOfInterestQry.MatterId) LEFT JOIN SumOfPaymentsMadeQry ON Matters.MatterId = SumOfPaymentsMadeQry.MatterId) LEFT JOIN DdSchedules ON Matters.MatterId = DdSchedules.MatterId
WHERE (((Clients.ClientGroup)=[Forms]![SelectClientGroupFrm]![ClientGroupFld]))
ORDER BY Clients.ClientId, Matters.ClientId;



Then output of the above is
HGI001.JPG

The columns with yellow highlight
  1. The PrincOrJudg column contents Appear intermittently - there doesn't seem to be any logic when they appear, but each time I run the report the same ones are not there
  2. The same with the Interest column
  3. The balance column appears as expected

The report is as follows - ANY HELP would be very appreciated

HGI002.JPG
 

Attachments

  • HGI001.JPG
    HGI001.JPG
    112.8 KB · Views: 194

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:04
Joined
May 7, 2009
Messages
19,169
did you set the Hide Duplicate (Design View->Property->Format) on textbox PrincOrJudgFld on your report to Yes. It should be set to No.
 

atrium

Registered User.
Local time
Today, 11:04
Joined
May 13, 2014
Messages
348
ARNELP you are a marvel. That was the problem Thanks very much
 

Users who are viewing this thread

Top Bottom