Filling in Blanks based on known data

[ckornoelje]

I have created a database, query and report that will take Bank CD rates, and
average them by term. There are several terms that banks do not post the
rates for. So I need to figure out the rates based on a difference between
the terms. No problem, I can do that manually. I am having touble telling
my report (or should I do this on the query)..to take the known information
and adjust it based on the difference. Example:

1 Mo Rate Not known
2 Month Rate Not Known
3 Mo CD is .35?%
4 Mo Rate Not Known
5 Mo Rate Not Known
6 Month CD is .89 %

If I know the avg difference is .13% How do I tell the system to calculate
based on the known terms and add or subtract the difference?

 

[Larry Daugherty]

You need to create a function procedure in a standard module that will
accept as parameters all of the information you now use to calculate
the return. The function must behave properly for elements that have
the correct values already as well as accepting the flags and
parameters to create the proper result when the rate is missing..
That function will return the "Rate" for all CDs.

You create a new field with a name followed by a colon, example -
Rate: That function will be called for each CD in your query. The
new field must be used instead of the old field that returned the
rate.

Post back if you have questions.

HTH

 

[ckornoelje]

Thank you Larry,

I am a bit of a novice I guess. I understand I need to create a new field.
My field that returned the rate is called APY. So I would create another
field in my query Rate: APY (?)

If that is correct, from here I am still lost. How would this expression be
formated, and is it on the report or query?

I have no a clue how to set up the expression to make this happen.

 

[Larry Daugherty]

As always, start your work by taking a backup and storing it safely
away.

You would create a new field but not one of exactly the same name. As
you''re doing all this, you're reworking the query. You will also
need to rework your form or report that is using that query to now use
the new field name.

The function procedure you need to create must accept parameters.
Those parameters will be exactly the same ones you use for your manual
calculations. They should be field names that already appear in your
query. Place the function declaration in a standard module. If no
standard module exists, create one with a meaningful name; for
example: basMyCalculations

The names you give your parameters in the declaration are irrelevant
placeholders. However, each parameter must have its type declared.

Look in Help for examples of declaring function procedures. The
function must be declared as being of a given type. That is the field
datatype that you want your result to be. Once your calculations are
done one of the last things you'll do in your function procedure is to
place the results of your calculations in the procedure name variable:
MyProcedure = MyResult. When your function procedure returns to the
point where it was called, it will have that value.

Using the QBE grid, the calling line would look something like the
following except that you'll use your real names

MyNewFielld: MyNewFunction(Parameter1, Parameter2)

Post back if you have questions.

HTH