# Thread: calculated value in a query

1. ## calculated value in a query

I'm not sure if this is possible but I think it should be. I'm new at coding so please be kind.

I'l start with what I want to acomplish and tell you what I have done. If you feel I'm going about it the wrong way please let me know.

I want to print a report (MS Access) using the data that is already displayed in a List Box. The list box is populated by a fuction that I wrote and the data is a mix of data taken from another list box and values that I compute on the fly. The number of entries in the list box varies.

The idea of the program is to compute interest due on a loan. The interest is compounded daily and the interest rate changes every quarter. I accomplished the omputation by creating a table that has starting, ending dates and interest rates. The first list box is filed by a query that list the aplicable rate for the aplicable period. The second list box selects each entry on the 1st list box and computes the interest due. Every time the interest is computed it is added to the next computation (row -1). Teh function that populates list box 2 uses an array to keep track of the interest computed and I look into the array to add the interest to the computation.

For exmple:

the interest rate from 01/01/2004 to 03/31/2004 is .04
the principle is \$200.00 and the number of days the balance is due is 67.
let's say that interest due is 2.21
then for the next preiod from 04/01/2004 to 06/30/2004 the interest
rate is .05
the next computation the principle is 202.21 and so on.

the interest of 2.21 is stored in the array at position 0,0. The next computation is made by looking up at position 0,0 and the results are stored at position 0,1. The array is 7,256 because I have other data stored there but the data is redondant as it is just a repeat of the data in list box 1.

The report needs to displayed the data stored in the array but I not able to acomplish this. I set the array as static but I think I should create a dynamic table to diplay the values.

With so many interest calculating programs out there I'm sure the solution should be a simple one but so far it eludes me.

Any help would be appreciated.

2. Inside Access itself you can call VBA functions from within a query, so I suggest that you just do that!

3. I did not know that was possible but a quick look at the tool bar showed me that it is. I will give that a try and see how it goes.

Thank you, I'll let you know.

4. Originally Posted by MarcusJT
Inside Access itself you can call VBA functions from within a query, so I suggest that you just do that!
That does not work as I cannot gaing access to the query itself.

I guess I did not make my problem clear. What I need is to make a query that looks up information on a table. Then the query calculates a value on the first result of the table and then uses that result to calculate every value after that.

It creates the query, then do a computetion on the first item but before it calcualtes the second it looks at the first to get the calculated answer and add it to the formula on the second calculation and so on. Always looking one back.

I can do it with a function and I get the results I need but once the results are on my list box I cannot get them to print out on a report.

I have tried to use the recordset.clone but I have not been able to get it to work. Any other suggestions?

5. 1) Modify your function to output the desired results to a temporary table

2) Call this function at the start of the report

3) Use the data in the temporary table to help draw your report (JOIN it or whatever you need to do)

4) At the end of the report delete the temporary table

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•