473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Query Problem With Calculated Field

88
Hello,

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
  1. 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
  2.  
  3. FROM TwoTier INNER JOIN [INDEX RATES] ON TwoTier.INDEX = [INDEX RATES].INDEX
  4.  
  5. WHERE (((TwoTier.POINTS)<>9.999));
The function that is not working is:

Expand|Select|Wrap|Line Numbers
  1. 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
Here is a copy of the function's code:

Expand|Select|Wrap|Line Numbers
  1. 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
  2.  
  3. fmt = "#0.000"
  4.  
  5. TERM = TERM * 12
  6.  
  7. tot = TERM
  8.  
  9. ReDim paymnt(TERM) As Double
  10.  
  11. 'payment = -1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100
  12.  
  13. For I = 1 To TERM
  14.  
  15. paymnt(I) = 0
  16.  
  17. Next I
  18.  
  19. 'paymnt(0) = points * bal / 100 + fees - bal
  20.  
  21. last = 1
  22.  
  23. test = R_CEIL
  24.  
  25. If (MARGIN + INDEXRATE) < R_CEIL Then
  26.  
  27.    test = (MARGIN + INDEXRATE)
  28.  
  29. End If
  30.  
  31. If HGI < 400 Or HGI > 759 Or HG = 490 Or HGI = 491 Then
  32.  
  33.    RTE_INC = 2
  34.  
  35.    For I = 1 To 12
  36.  
  37.       paymnt(I) = Payment
  38.  
  39.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  40.  
  41.    Next I
  42.  
  43.    last = last + 12
  44.  
  45.    TERM = TERM - 12
  46.  
  47.    RATE = RATE + RTE_INC
  48.  
  49.    While RATE < test
  50.  
  51.    Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
  52.  
  53.    For I = last To last + 11
  54.  
  55.       paymnt(I) = Payment
  56.  
  57.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  58.  
  59.    Next I
  60.  
  61.    last = last + 12
  62.  
  63.    TERM = TERM - 12
  64.  
  65.    RATE = RATE + RTE_INC
  66.  
  67.    Wend
  68.  
  69. Else
  70.  
  71.    If HGI > 674 And HGI < 690 Then
  72.  
  73.    RTE_INC = 2
  74.  
  75.    For I = 1 To 36
  76.  
  77.       paymnt(I) = Payment
  78.  
  79.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  80.  
  81.    Next I
  82.  
  83.    last = 37
  84.  
  85.    TERM = TERM - 36
  86.  
  87.    RATE = RATE + RTE_INC
  88.  
  89.    While RATE < test
  90.  
  91.    Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
  92.  
  93.    For I = last To last + 11
  94.  
  95.       paymnt(I) = Payment
  96.  
  97.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  98.  
  99.    Next I
  100.  
  101.    last = last + 12
  102.  
  103.    TERM = TERM - 12
  104.  
  105.    RATE = RATE + RTE_INC
  106.  
  107.    Wend
  108.  
  109. Else
  110.  
  111.    If HGI > 619 And HGI < 640 Then
  112.  
  113.    RTE_INC = 2
  114.  
  115.    For I = 1 To 60
  116.  
  117.       paymnt(I) = Payment
  118.  
  119.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  120.  
  121.    Next I
  122.  
  123.    last = 61
  124.  
  125.    TERM = TERM - 60
  126.  
  127.    RATE = RATE + RTE_INC
  128.  
  129.    While RATE < test
  130.  
  131.    Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
  132.  
  133.    For I = last To last + 11
  134.  
  135.       paymnt(I) = Payment
  136.  
  137.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  138.  
  139.    Next I
  140.  
  141.    last = last + 12
  142.  
  143.    TERM = TERM - 12
  144.  
  145.    RATE = RATE + RTE_INC
  146.  
  147.    Wend
  148.  
  149. Else
  150.  
  151.    If HGI > 639 And HGI < 650 Then
  152.  
  153.    RTE_INC = 2
  154.  
  155.    For I = 1 To 84
  156.  
  157.       paymnt(I) = Payment
  158.  
  159.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  160.  
  161.    Next I
  162.  
  163.    last = 85
  164.  
  165.    TERM = TERM - 84
  166.  
  167.    RATE = RATE + RTE_INC
  168.  
  169.    While RATE < test
  170.  
  171.    Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
  172.  
  173.    For I = last To last + 11
  174.  
  175.       paymnt(I) = Payment
  176.  
  177.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  178.  
  179.    Next I
  180.  
  181.    last = last + 12
  182.  
  183.    TERM = TERM - 12
  184.  
  185.    RATE = RATE + RTE_INC
  186.  
  187.    Wend
  188.  
  189. Else
  190.  
  191.    If HGI > 689 And HGI < 700 Then
  192.  
  193.    RTE_INC = 2
  194.  
  195.    For I = 1 To 120
  196.  
  197.       paymnt(I) = Payment
  198.  
  199.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  200.  
  201.    Next I
  202.  
  203.    last = 121
  204.  
  205.    TERM = TERM - 120
  206.  
  207.    RATE = RATE + RTE_INC
  208.  
  209.    While RATE < test
  210.  
  211.    Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
  212.  
  213.    For I = last To last + 11
  214.  
  215.       paymnt(I) = Payment
  216.  
  217.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  218.  
  219.    Next I
  220.  
  221.    last = last + 12
  222.  
  223.    TERM = TERM - 12
  224.  
  225.    RATE = RATE + RTE_INC
  226.  
  227.    Wend
  228.  
  229. Else
  230.  
  231.    If HGI > 719 And HGI < 740 Then
  232.  
  233.    For I = 1 To 60
  234.  
  235.       paymnt(I) = Payment
  236.  
  237.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  238.  
  239.    Next I
  240.  
  241.    last = 61
  242.  
  243.    TERM = TERM - 60
  244.  
  245. Else
  246.  
  247.    If HGI > 699 And HGI < 720 Then
  248.  
  249.    For I = 1 To 84
  250.  
  251.       paymnt(I) = Payment
  252.  
  253.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  254.  
  255.    Next I
  256.  
  257.    last = 85
  258.  
  259.    TERM = TERM - 84
  260.  
  261. Else
  262.  
  263.    If HGI > 399 And HGI < 535 Then
  264.  
  265.    RTE_INC = 2
  266.  
  267.       For I = 1 To 6
  268.  
  269.       paymnt(I) = Payment
  270.  
  271.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  272.  
  273.       Next I
  274.  
  275.       last = 7
  276.  
  277.       TERM = TERM - 6
  278.  
  279.    RATE = RATE + RTE_INC / 2
  280.  
  281.    While RATE < test
  282.  
  283.    Payment = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
  284.  
  285.    For I = last To last + 5
  286.  
  287.       paymnt(I) = Payment
  288.  
  289.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  290.  
  291.    Next I
  292.  
  293.    last = last + 6
  294.  
  295.    TERM = TERM - 6
  296.  
  297.    RATE = RATE + RTE_INC
  298.  
  299.    Wend
  300.  
  301. Else
  302.  
  303.    If HGI > 534 And HGI < 550 Then
  304.  
  305.       For I = 1 To 6
  306.  
  307.       paymnt(I) = Payment
  308.  
  309.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  310.  
  311.       Next I
  312.  
  313.    last = 7
  314.  
  315.    TERM = TERM - 6
  316.  
  317.    For I = last To last + 5
  318.  
  319.       TERM = TERM - 1
  320.  
  321.       paymnt(I) = Payment
  322.  
  323.       RATE = RATE + RTE_INC / 12
  324.  
  325.       If RATE > test Then
  326.  
  327.       RATE = test
  328.  
  329.       End If
  330.  
  331.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  332.  
  333.    Next I
  334.  
  335.    last = 13
  336.  
  337.    payment1 = -1 * Int(100 * Pmt(RATE / 1200, TERM, BAL, 0, 0) + 0.5) / 100
  338.  
  339.    'While rate < test Or last Mod 12 <> 1 Or (-1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100) > payment
  340.  
  341.    If last Mod 12 = 1 Then
  342.  
  343.    'payment1 = -1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100
  344.  
  345.    If payment1 > Payment * (1 + PAY_INC / 100) Then
  346.  
  347.       payment1 = Payment * (1 + PAY_INC / 100)
  348.  
  349.    End If
  350.  
  351.    Payment = payment1
  352.  
  353.    End If
  354.  
  355.    BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  356.  
  357.    'paymnt(last) = payment
  358.  
  359.    last = last + 1
  360.  
  361.    TERM = TERM - 1
  362.  
  363.    RATE = RATE + RTE_INC / 12
  364.  
  365.    If RATE > test Then
  366.  
  367.    RATE = test
  368.  
  369.    End If
  370.  
  371.    'Wend
  372.  
  373. Else
  374.  
  375.    If (HGI > 549 And HGI < 561) Or (HGI > 597 And HGI < 602) Then
  376.  
  377.       For I = 1 To 3
  378.  
  379.       paymnt(I) = Payment
  380.  
  381.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  382.  
  383.       Next I
  384.  
  385.    last = 4
  386.  
  387.    TERM = TERM - 3
  388.  
  389.    For I = last To last + 8
  390.  
  391.       TERM = TERM - 1
  392.  
  393.       paymnt(I) = Payment
  394.  
  395.       RATE = RATE + RTE_INC / 12
  396.  
  397.       If RATE > test Then
  398.  
  399.       RATE = test
  400.  
  401.       End If
  402.  
  403.       BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  404.  
  405.    Next I
  406.  
  407.    last = 13
  408.  
  409.    TERM = TERM - 1
  410.  
  411.    RATE = RATE + RTE_INC / 12
  412.  
  413.    If RATE > test Then
  414.  
  415.    RATE = test
  416.  
  417.    End If
  418.  
  419.    'payment1 = -1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100
  420.  
  421.    'While rate < test Or last Mod 12 <> 1 Or (-1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100) > payment
  422.  
  423.    'If last Mod 12 = 1 Then
  424.  
  425.    'payment1 = -1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100
  426.  
  427.    'If payment1 > payment * (1 + pay_inc / 100) Then
  428.  
  429.    '   payment1 = payment * (1 + pay_inc / 100)
  430.  
  431.    'End If
  432.  
  433.    'payment = payment1
  434.  
  435.    'End If
  436.  
  437.    'bal = Int(100 * (bal - payment + bal * rate / 1200) + .5) / 100
  438.  
  439.    'paymnt(last) = payment
  440.  
  441.    'last = last + 1
  442.  
  443.    'term = term - 1
  444.  
  445.    'rate = rate + rte_inc / 12
  446.  
  447.    'If rate > test Then
  448.  
  449.    'rate = test
  450.  
  451.    'End If
  452.  
  453.    'Wend
  454.  
  455. End If
  456.  
  457. End If
  458.  
  459. End If
  460.  
  461. End If
  462.  
  463. End If
  464.  
  465. End If
  466.  
  467. End If
  468.  
  469. End If
  470.  
  471. End If
  472.  
  473. End If
  474.  
  475.    RATE = test
  476.  
  477.    'payment = -1 * Int(100 * pmt(rate / 1200, term, bal, 0, 0) + .5) / 100
  478.  
  479.    For I = last To tot
  480.  
  481.    paymnt(I) = Payment
  482.  
  483.    BAL = Int(100 * (BAL - Payment + BAL * RATE / 1200) + 0.5) / 100
  484.  
  485.    Next I
  486.  
  487. adjust2 = Format((Int(1000 * (IRR(paymnt(), (0.01 * RATE / 12)) * 1200) + 0.5) / 1000), fmt)
  488.  
  489. End Function
I did not write this function or query so I cannot begin to explain why it is doing the calculations it is. I do know that the function fails on the last line when it is trying to return the value because the RATE value in the IRR function is zero, and from what I know the "guess" in the IRR function cannot have a zero value. So I tracked it back to the fact that the RATE is comming into the function as zero even though there might be a value like 6.25 in the table. I figure if I can get the query to actually pass all the values into the function then I can truly start working on fixing the function if there is anything wrong with it.

Any help is appreciated. Thanks!
Oct 4 '07 #1
3 1592
NeoPa
32,556 Expert Mod 16PB
Well, this is clearly too much for anyone to want to look at for you.
You will need to do some work to break this down first. I expect that won't be easy as even understanding what someone else has written is always difficult. Imagine then how much of a task you've posted here.

It comes back to you I'm afraid. Maybe we can help with tips on how to start debugging something like this. Don't forget - we don't even have a clue as to the data (and the formatting of the code with some, unreliable, indentation is very sloppy).

Do you know anything about debugging VBA?
See Debugging in VBA for starters.
Oct 5 '07 #2
fperri
88
Well, this is clearly too much for anyone to want to look at for you.
You will need to do some work to break this down first. I expect that won't be easy as even understanding what someone else has written is always difficult. Imagine then how much of a task you've posted here.

It comes back to you I'm afraid. Maybe we can help with tips on how to start debugging something like this. Don't forget - we don't even have a clue as to the data (and the formatting of the code with some, unreliable, indentation is very sloppy).

Do you know anything about debugging VBA?
See Debugging in VBA for starters.
Yeah, its a mess. For starters it doesn't assign any value to the payment variable that is used throughout the function which I'm thinking (or guessing at this point) is why the last line buggs out.

I need to re-write it completely because when I try to compile I get issue after issue with the syntax. Come to find out the code was migrated from Access 2.0 which I didn't even know existed. Only thing is I can't even find anyone within the company that can accurately remember what the function is supposed to be doing so I am going to have to try and write out the logic to see if I can figure it out.

I figured I'd give it a try by posting it. Your right, it is quite large to assume anyone would want to spend their free time looking at it. I was really just hoping that it was something small that I just wasn't seeing.
Oct 9 '07 #3
NeoPa
32,556 Expert Mod 16PB
If you can find some smaller pieces of the puzzle we may be able to help with that. Otherwise just reading the code across the web (without access to other info) is a pretty large task.

Is it necessary to have all the blank lines between the code?
Is it possible to get the code reformatted and properly indented? (Tip: I would advise you do this anyway for two reasons :
  1. It will help you to understand it and become familiar with it.
  2. It will be easier for you to work with afterwards.)
I try to go through inherited code with a fine-tooth comb when I first take it on. I find it helps (but it's rarely fun taking on someone else's code).
Good luck anyway.
Oct 9 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Steve | last post by:
Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate field's criteria is set ats: Forms!FrmRestock!LastXDays. LastXDays on the form is a combobox where the selections are 30, 60...
3
by: Bill Clark | last post by:
I have about 20,000 records pulled from Excel that I need to update. What I need to do is run an update query that bascially says: If a field is null, update it with the previous record value of...
5
by: MX1 | last post by:
I have query2 that sums a set of fields from query1. Works fine. However, when I try to add two of the calculated fields from query2, I get prompted for the label I gave the fields. I just hit...
1
by: Norbert Lieckfeldt | last post by:
MS Access 2002 here. I am just trying to set up a simple database for a friend who's an optician. Basically, all clients with address details, date of last eyetest and a drop-down combo box to...
2
by: Norbert Lieckfeldt | last post by:
I am setting up a database for a friend who's an Optician, using MS Access 2002. All seems to be working well, but I have hit a snag. There's a calculated field both in a form and a query which...
2
by: Julie Wardlow | last post by:
Help! I am calculating a future date using the DateAdd function in a query (the calculation also involves an IIf statement), and have managed to get this formula to produce the required result....
9
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped....
10
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this...
4
by: tweeterbot | last post by:
Hi, I am a chemical engineer trying to design a database and I am running into some trouble. My database is going to be 'processing' raw data to get the figures we need to prepare the monthly...
3
by: myemail.an | last post by:
I use Access 2007 and create queries with a number of calculated fields/expressions (I'm still a novice so please forgive me if my wording is imprecise...), like: MyCalculation = Field1 - Field2. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.