J
Joker via AccessMonster.com
Hello,
I am having a problem with a make table query expression that is relatively a
simple calculation. The expression that is returning null is for Calc
Balance:
Calc Balance: IIf([tbl_CutoffImport]![END SCH BAL] Is Null,"0",((
[tbl_CutoffImport]![Ending UPB]+[tbl_ppdPrinSum]![SumOfPrinSplit]+
[tbl_CutoffImport]![LIQUIDATIONS])-([tbl_DelinqPrinSum]![SumOfPrinSplit]-
[tbl_CutoffImport]![TOTAL PRIN]-[tbl_CutoffImport]![OTHER]-[tbl_CutoffImport]!
[INT ADJ])))
Here is the SQL of my query
SELECT DISTINCT tbl_CutoffImport.CMMCLnNo AS LoanNum, PrivateTrialBal.InvNo,
tbl_CutoffImport.[Ending UPB] AS [Actual UPB], tbl_CutoffImport.[TOTAL PRIN]
AS [Sched Princ], tbl_CutoffImport.[INT ADJ], tbl_CutoffImport.OTHER,
tbl_CutoffImport.[END SCH BAL] AS [System Balance], tbl_CutoffImport.
SchPrinBal AS upb, PrivateTrialBal.PartFlag AS Participant, tbl_DelinqPrinSum!
SumOfPrinSplit AS delq, tbl_ppdPrinSum!SumOfPrinSplit AS ppd,
tbl_CutoffImport!LIQUIDATIONS AS liquid, PrivateDelqInst.DelRsn AS [Stop Code]
, IIf([Stop Code] Is Null,"N","Y") AS [Stop?], IIf([tbl_CutoffImport]![END
SCH BAL] Is Null,"0",(([tbl_CutoffImport]![Ending UPB]+[tbl_ppdPrinSum]!
[SumOfPrinSplit]+[tbl_CutoffImport]![LIQUIDATIONS])-([tbl_DelinqPrinSum]!
[SumOfPrinSplit]-[tbl_CutoffImport]![TOTAL PRIN]-[tbl_CutoffImport]![OTHER]-
[tbl_CutoffImport]![INT ADJ]))) AS [Calc Balance], IIf(tbl_DelinqPrinSum!
SumOfPrinSplit Is Null,tbl_CutoffImport![TOTAL PRIN],tbl_CutoffImport![TOTAL
PRIN]+tbl_DelinqPrinSum!SumOfPrinSplit) AS pmt, tbl_CutoffImport.[END SCH BAL]
AS [sch bal], ([Calc Balance]-tbl_CutoffImport![END SCH BAL]) AS difference,
([upb]-[pmt]) AS Total, ([Total]-[sch bal]) AS [Pool To Sec], ([difference]-
[Pool To Sec]) AS [Diff Pool & Diff] INTO tbl_PTSReport
FROM ((((tbl_CutoffImport LEFT JOIN PrivatePrepaidLoans ON tbl_CutoffImport.
CMMCLnNo = PrivatePrepaidLoans.SvcLnNo) LEFT JOIN PrivateTrialBal ON
tbl_CutoffImport.CMMCLnNo = PrivateTrialBal.CMMCLnNo) LEFT JOIN
PrivateDelqInst ON tbl_CutoffImport.CMMCLnNo = PrivateDelqInst.LoanNo) LEFT
JOIN tbl_DelinqPrinSum ON tbl_CutoffImport.CMMCLnNo = tbl_DelinqPrinSum.
LoanNo) LEFT JOIN tbl_ppdPrinSum ON tbl_CutoffImport.CMMCLnNo =
tbl_ppdPrinSum.SvcLnNo
WHERE (((PrivateTrialBal.InvNo) Is Not Null));
The field for all records is just returning completely blank. Any thoughts
will be greatly appreciated. Thanks.
I am having a problem with a make table query expression that is relatively a
simple calculation. The expression that is returning null is for Calc
Balance:
Calc Balance: IIf([tbl_CutoffImport]![END SCH BAL] Is Null,"0",((
[tbl_CutoffImport]![Ending UPB]+[tbl_ppdPrinSum]![SumOfPrinSplit]+
[tbl_CutoffImport]![LIQUIDATIONS])-([tbl_DelinqPrinSum]![SumOfPrinSplit]-
[tbl_CutoffImport]![TOTAL PRIN]-[tbl_CutoffImport]![OTHER]-[tbl_CutoffImport]!
[INT ADJ])))
Here is the SQL of my query
SELECT DISTINCT tbl_CutoffImport.CMMCLnNo AS LoanNum, PrivateTrialBal.InvNo,
tbl_CutoffImport.[Ending UPB] AS [Actual UPB], tbl_CutoffImport.[TOTAL PRIN]
AS [Sched Princ], tbl_CutoffImport.[INT ADJ], tbl_CutoffImport.OTHER,
tbl_CutoffImport.[END SCH BAL] AS [System Balance], tbl_CutoffImport.
SchPrinBal AS upb, PrivateTrialBal.PartFlag AS Participant, tbl_DelinqPrinSum!
SumOfPrinSplit AS delq, tbl_ppdPrinSum!SumOfPrinSplit AS ppd,
tbl_CutoffImport!LIQUIDATIONS AS liquid, PrivateDelqInst.DelRsn AS [Stop Code]
, IIf([Stop Code] Is Null,"N","Y") AS [Stop?], IIf([tbl_CutoffImport]![END
SCH BAL] Is Null,"0",(([tbl_CutoffImport]![Ending UPB]+[tbl_ppdPrinSum]!
[SumOfPrinSplit]+[tbl_CutoffImport]![LIQUIDATIONS])-([tbl_DelinqPrinSum]!
[SumOfPrinSplit]-[tbl_CutoffImport]![TOTAL PRIN]-[tbl_CutoffImport]![OTHER]-
[tbl_CutoffImport]![INT ADJ]))) AS [Calc Balance], IIf(tbl_DelinqPrinSum!
SumOfPrinSplit Is Null,tbl_CutoffImport![TOTAL PRIN],tbl_CutoffImport![TOTAL
PRIN]+tbl_DelinqPrinSum!SumOfPrinSplit) AS pmt, tbl_CutoffImport.[END SCH BAL]
AS [sch bal], ([Calc Balance]-tbl_CutoffImport![END SCH BAL]) AS difference,
([upb]-[pmt]) AS Total, ([Total]-[sch bal]) AS [Pool To Sec], ([difference]-
[Pool To Sec]) AS [Diff Pool & Diff] INTO tbl_PTSReport
FROM ((((tbl_CutoffImport LEFT JOIN PrivatePrepaidLoans ON tbl_CutoffImport.
CMMCLnNo = PrivatePrepaidLoans.SvcLnNo) LEFT JOIN PrivateTrialBal ON
tbl_CutoffImport.CMMCLnNo = PrivateTrialBal.CMMCLnNo) LEFT JOIN
PrivateDelqInst ON tbl_CutoffImport.CMMCLnNo = PrivateDelqInst.LoanNo) LEFT
JOIN tbl_DelinqPrinSum ON tbl_CutoffImport.CMMCLnNo = tbl_DelinqPrinSum.
LoanNo) LEFT JOIN tbl_ppdPrinSum ON tbl_CutoffImport.CMMCLnNo =
tbl_ppdPrinSum.SvcLnNo
WHERE (((PrivateTrialBal.InvNo) Is Not Null));
The field for all records is just returning completely blank. Any thoughts
will be greatly appreciated. Thanks.