I have a calculated field in my query that uses a function to come up with the value. The function has various fields from the table used in the query passed into it as parameters. For some reason, it pulls the values of a few of the fields for a few of the parameters, but for the others it is assigning the parameter the value of zero instead of what is actualy in that field. I have even tracked one of the fields back to the table and there is not one record that has a zero value for that field. I am kind of at a loss as to why it is doing this for some and not for others. The primary field that I am concerned with is the RATE field.
Here is a copy of my query.
Expand|Select|Wrap|Line Numbers
- SELECT DISTINCTROW TwoTier.*, [Rate]-[LRate] AS R_Change, [Points]-[LPoints] AS P_Change, YIELD2([Rate],[Points],[HGI]) AS Yeild1, YIELD1([Rate],[Points],[HGI]) AS Yeild2, [Points]-[Neg_disc] AS A_Points, APR2([RATE],[TERM],([Tier_Max]/2),[POINTS],[FEES]) AS APR1, adjust2([TwoTier].[HGI],[INDEX RATES].[INDEXRATE],[TwoTier].[MARGIN],[TwoTier].[RATE],[A_Points],[TwoTier].[R_CEIL],([TwoTier].[TIER_MAX]/2),[TwoTier].[FEES],[TwoTier].[TERM],[TwoTier].[RATE_CAP],[TwoTier].[RTE_INC],[TwoTier].[PAY_INC],[TwoTier].[FST_RTE_AD],[TwoTier].[OTH_RTE_AD]) AS APR_USB, IIf([Rate_Type]="v",Left([Loan_Num],5),"") AS 1st_Rate_Adj INTO ACC0130
- FROM TwoTier INNER JOIN [INDEX RATES] ON TwoTier.INDEX = [INDEX RATES].INDEX
- WHERE (((TwoTier.POINTS)<>9.999));
Expand|Select|Wrap|Line Numbers
- adjust2([TwoTier].[HGI],[INDEX RATES].[INDEXRATE],[TwoTier].[MARGIN],[TwoTier].[RATE],[A_Points],[TwoTier].[R_CEIL],([TwoTier].[TIER_MAX]/2),[TwoTier].[FEES],[TwoTier].[TERM],[TwoTier].[RATE_CAP],[TwoTier].[RTE_INC],[TwoTier].[PAY_INC],[TwoTier].[FST_RTE_AD],[TwoTier].[OTH_RTE_AD]) AS APR_USB
Expand|Select|Wrap|Line Numbers
- Function adjust2(HGI, INDEXRATE, MARGIN, RATE, POINTS, R_CEIL, BAL, FEES, TERM, RATE_CAP, RTE_INC, PAY_INC, FST_RTE_AD, OTH_RTE_AD) As Double
- fmt = "#0.000"
- TERM = TERM * 12
- tot = TERM
- ReDim paymnt(TERM) As Double
- 'payment = -1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100
- For I = 1 To TERM
- paymnt(I) = 0
- Next I
- 'paymnt(0) = points * bal / 100 + fees - bal
- last = 1
- test = R_CEIL
- If (MARGIN + INDEXRATE) < R_CEIL Then
- test = (MARGIN + INDEXRATE)
- End If
- If HGI < 400 Or HGI > 759 Or HG = 490 Or HGI = 491 Then
- RTE_INC = 2
- For I = 1 To 12
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = last + 12
- TERM = TERM - 12
- RATE = RATE + RTE_INC
- While RATE < test
- Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
- For I = last To last + 11
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = last + 12
- TERM = TERM - 12
- RATE = RATE + RTE_INC
- Wend
- Else
- If HGI > 674 And HGI < 690 Then
- RTE_INC = 2
- For I = 1 To 36
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 37
- TERM = TERM - 36
- RATE = RATE + RTE_INC
- While RATE < test
- Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
- For I = last To last + 11
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = last + 12
- TERM = TERM - 12
- RATE = RATE + RTE_INC
- Wend
- Else
- If HGI > 619 And HGI < 640 Then
- RTE_INC = 2
- For I = 1 To 60
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 61
- TERM = TERM - 60
- RATE = RATE + RTE_INC
- While RATE < test
- Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
- For I = last To last + 11
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = last + 12
- TERM = TERM - 12
- RATE = RATE + RTE_INC
- Wend
- Else
- If HGI > 639 And HGI < 650 Then
- RTE_INC = 2
- For I = 1 To 84
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 85
- TERM = TERM - 84
- RATE = RATE + RTE_INC
- While RATE < test
- Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
- For I = last To last + 11
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = last + 12
- TERM = TERM - 12
- RATE = RATE + RTE_INC
- Wend
- Else
- If HGI > 689 And HGI < 700 Then
- RTE_INC = 2
- For I = 1 To 120
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 121
- TERM = TERM - 120
- RATE = RATE + RTE_INC
- While RATE < test
- Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
- For I = last To last + 11
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = last + 12
- TERM = TERM - 12
- RATE = RATE + RTE_INC
- Wend
- Else
- If HGI > 719 And HGI < 740 Then
- For I = 1 To 60
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 61
- TERM = TERM - 60
- Else
- If HGI > 699 And HGI < 720 Then
- For I = 1 To 84
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 85
- TERM = TERM - 84
- Else
- If HGI > 399 And HGI < 535 Then
- RTE_INC = 2
- For I = 1 To 6
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 7
- TERM = TERM - 6
- RATE = RATE + RTE_INC / 2
- While RATE < test
- Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
- For I = last To last + 5
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = last + 6
- TERM = TERM - 6
- RATE = RATE + RTE_INC
- Wend
- Else
- If HGI > 534 And HGI < 550 Then
- For I = 1 To 6
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 7
- TERM = TERM - 6
- For I = last To last + 5
- TERM = TERM - 1
- paymnt(I) = Payment
- RATE = RATE + RTE_INC / 12
- If RATE > test Then
- RATE = test
- End If
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 13
- payment1 = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
- 'While rate < test Or last Mod 12 <> 1 Or (-1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100) > payment
- If last Mod 12 = 1 Then
- 'payment1 = -1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100
- If payment1 > Payment * (1 + PAY_INC / 100) Then
- payment1 = Payment * (1 + PAY_INC / 100)
- End If
- Payment = payment1
- End If
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- 'paymnt(last) = payment
- last = last + 1
- TERM = TERM - 1
- RATE = RATE + RTE_INC / 12
- If RATE > test Then
- RATE = test
- End If
- 'Wend
- Else
- If (HGI > 549 And HGI < 561) Or (HGI > 597 And HGI < 602) Then
- For I = 1 To 3
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 4
- TERM = TERM - 3
- For I = last To last + 8
- TERM = TERM - 1
- paymnt(I) = Payment
- RATE = RATE + RTE_INC / 12
- If RATE > test Then
- RATE = test
- End If
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- last = 13
- TERM = TERM - 1
- RATE = RATE + RTE_INC / 12
- If RATE > test Then
- RATE = test
- End If
- 'payment1 = -1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100
- 'While rate < test Or last Mod 12 <> 1 Or (-1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100) > payment
- 'If last Mod 12 = 1 Then
- 'payment1 = -1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100
- 'If payment1 > payment * (1 + pay_inc / 100) Then
- ' payment1 = payment * (1 + pay_inc / 100)
- 'End If
- 'payment = payment1
- 'End If
- 'bal = Int(100 * (bal - payment + bal * rate / 1200) + .5) / 100
- 'paymnt(last) = payment
- 'last = last + 1
- 'term = term - 1
- 'rate = rate + rte_inc / 12
- 'If rate > test Then
- 'rate = test
- 'End If
- 'Wend
- End If
- End If
- End If
- End If
- End If
- End If
- End If
- End If
- End If
- End If
- RATE = test
- 'payment = -1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100
- For I = last To tot
- paymnt(I) = Payment
- BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
- Next I
- adjust2 = Format((Int(1000 * (IRR(paymnt(), (0.01 * RATE / 12)) * 1200) + 0.5) / 1000), fmt)
- End Function
Any help is appreciated. Thanks!