Make table query not returning value for expression

  • Thread starter Joker via AccessMonster.com
  • Start date
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.
 
A

Allen Browne

If any one of those fields is null, the result will be null.
Therefore you need Nz() around each one to convert it to zero.

Something like this:

Calc Balance: CCur(IIf([tbl_CutoffImport]![END SCH BAL] Is Null, 0,
Nz([tbl_CutoffImport]![Ending UPB],0) +
Nz([tbl_ppdPrinSum]![SumOfPrinSplit],0) +
Nz([tbl_CutoffImport]![LIQUIDATIONS],0) -
Nz([tbl_DelinqPrinSum]![SumOfPrinSplit],0) +
Nz([tbl_CutoffImport]![TOTAL PRIN],0) +
Nz([tbl_CutoffImport]![OTHER],0) +
Nz([tbl_CutoffImport]![INT ADJ],0)))

Note that since this is a numeric value, the quotes around the zero need to
be removed. Access may still misunderstand the result; hence the entire
expression is wrapped in CCur() or CDbl() or CLng() or whatever.

For more information, see Error #2 in:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Joker via AccessMonster.com said:
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.
Click to expand...
 
S

steinmetzw42 via AccessMonster.com

Thank you very much. I started out with Nz() on the whole thing which was
giving me a Data Type Mismatch error since not every field was going to be
filled. I needed to add the Nz() to just one of the fields and it worked
perfectly.

Nz([tbl_ppdPrinSum]![SumOfPrinSplit])

Thats all it took. Thanks again Mr. Browne.

Allen said:
If any one of those fields is null, the result will be null.
Therefore you need Nz() around each one to convert it to zero.

Something like this:

Calc Balance: CCur(IIf([tbl_CutoffImport]![END SCH BAL] Is Null, 0,
Nz([tbl_CutoffImport]![Ending UPB],0) +
Nz([tbl_ppdPrinSum]![SumOfPrinSplit],0) +
Nz([tbl_CutoffImport]![LIQUIDATIONS],0) -
Nz([tbl_DelinqPrinSum]![SumOfPrinSplit],0) +
Nz([tbl_CutoffImport]![TOTAL PRIN],0) +
Nz([tbl_CutoffImport]![OTHER],0) +
Nz([tbl_CutoffImport]![INT ADJ],0)))

Note that since this is a numeric value, the quotes around the zero need to
be removed. Access may still misunderstand the result; hence the entire
expression is wrapped in CCur() or CDbl() or CLng() or whatever.

For more information, see Error #2 in:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html
Hello,
Click to expand...
[quoted text clipped - 50 lines]
thoughts
will be greatly appreciated. Thanks.
Click to expand...
Click to expand...
 
J

John W. Vinson

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])))
Click to expand...

If ANY field in an expression is NULL, the entire expression will be NULL. Try
wrapping each term of the expression in the NZ() function to convert Null To
Zero - that is, instead of [tbl_CutoffImport]![Ending UPB] use
NZ([tbl_CutoffImport]![Ending UPB]).

John W. Vinson [MVP]
 
Top