Query to return a module

  • Thread starter Thread starter Johanna Gronlund
  • Start date Start date
J

Johanna Gronlund

Hello,

I have inherited a database which contains complex calculations that are
done in 'modules'. They are all under one function called 'returncost'. I
would like to display this field on my query but do not how to do this. Can
anyone help, please?

Thanks,

Johanna
 
Hello,

I have inherited a database which contains complex calculations that are
done in 'modules'. They are all under one function called 'returncost'. I
would like to display this field on my query but do not how to do this. Can
anyone help, please?

Thanks,

Johanna
Click to expand...

Not without knowing what "returncost" is and does, no.
 
Sorry - I should have been more specific with my question.

The database contains information on service providers, what services they
offer and how much they should be paid (beginning of year and year end).

"ReturnCost" calculates (for each provider) the amount of money that the
provider should be paid in the beginning of the year. They inform us of the
amount of activity they are likely to carry out during the coming year.
Different services have different equations, for example:
activity * price
(activity * price)+annual fee
etc.
"ReturnCost" is used to calculate these costs.

This information already comes through to a report but I need it in a query
as well. Is this possible? Please let me know if you need more information or
if my explanation isn't clear. I'm very un-used to things on modules/VB and
waiting for my training.

Many thanks in advance,

Johanna
 
Sorry - I should have been more specific with my question.

The database contains information on service providers, what services they
offer and how much they should be paid (beginning of year and year end).

"ReturnCost" calculates (for each provider) the amount of money that the
provider should be paid in the beginning of the year. They inform us of the
amount of activity they are likely to carry out during the coming year.
Different services have different equations, for example:
activity * price
(activity * price)+annual fee
etc.
"ReturnCost" is used to calculate these costs.

This information already comes through to a report but I need it in a query
as well. Is this possible? Please let me know if you need more information or
if my explanation isn't clear. I'm very un-used to things on modules/VB and
waiting for my training.

Many thanks in advance,

Johanna
Click to expand...

A Report is a "dead end", output only medium. You can't (at all easily) "read"
data from a report.

However, if you do a calculation in a Report - such as calling a function or
using an expression in a textbox's control source - then you can do the same
in a query.

Again: you told me what ReturnCost calculates.

You did not tell me HOW ReturnCost calculates it. Based on what you posted I
simply don't know whether ReturnCost is the name of a Module, the name of a
sub or a function in a module, the name of a textbox on a Report, the label of
a textbox on a Report, or what! Remember, you can see your database - we
can't!
 
ReturnCost is the name of the module. I'll paste an example of what is in the
module below:

Function returncost(whatles As String, listsize As Double, listsizeu16 As
Integer, listsize35_75 As Integer, auditrv As Integer, activity As Double,
Price As Double, Annual As Double, EstActivity As Double) As Double

Select Case whatles
Case "DES01"
returncost = Des01Calc(auditrv, activity, EstActivity, Price)
Case "DES02"
returncost = 0
Case "DES03A"
returncost = ActCalc(activity, Price)
Case "DES03B"
returncost = ActCalc(activity, Price)
Case "DES04A"
returncost = ActCalc(activity, Price)
Case "DES04B"
returncost = ActCalc(activity, Price)
Case "DES04C"
returncost = ActCalc(activity, Price)
Case "DES06"
returncost = ActCalc(activity, Price)
Case "DES07 - 1"
returncost = Des07Calc(activity, Price, auditrv)
Case "DES07 - 2"
returncost = Des07Calc(activity, Price, auditrv)
Case "NES01A"
returncost = Annual
etc.

End Select
End Function

Function Des01Calc(auditrv As Integer, activity As Double, EstActivity As
Double, Price As Double) As Double

If auditrv = 2 Then ' audit received and verified
Des01Calc = (EstActivity * Price) + (1.37 * activity)
Else
Des01Calc = (EstActivity * Price)
End If

End Function


Function Des07Calc(activity As Double, Price As Double, auditrv As Integer)
As Double

Des07Calc = (activity * Price)
If auditrv = 2 Then
Des07Calc = Des07Calc + (activity * Price)
End If

End Function
 
ReturnCost is the name of the module. I'll paste an example of what is in the
module below:
Click to expand...

Well, it appears that it's the name of a function *IN* a Module rather than
the name of the Module. Note that these names *must* be different; Access will
throw an error if you have a function named Returncost and also a function
named Returncost. I'll ususally name the Module (in the Modules tab) as
something like basReturnCost.
Function returncost(whatles As String, listsize As Double, listsizeu16 As
Integer, listsize35_75 As Integer, auditrv As Integer, activity As Double,
Price As Double, Annual As Double, EstActivity As Double) As Double
Click to expand...

You can put a calculated field in a Query by typing

TheReturnCost: Returncost([thisfield], [thatfield], 20, 60, <etc>)

The values in the parentheses must match the parameters in the function
definition (a String, a Double, three integers, four more Doubles); they can
be the names of other fields in the query, or they can be literal numbers or
strings. Since I don't know the meanings of any of these values or where they
should come from I can't tell which arguments should get what values though!
 
Back
Top