Calculated fields

[FinChase]

I have a query that needs to calculate a cost in a field using a variable formula based on a criteria selected in a form. I had no problem creating a calculated field when there was just a single formula involved. Now, however, I have several different formulas, and it seems too complex for an IIf or Switch statement.

I thought it would be best if I created public functions for each formula. I did this and when tested they work. My problem now is figuring out how call that function based on criteria.

For example, if a user selects "NENUA" in a list box on the form, the calculated field needs to use public function Form_O65, but if the user selects NENUF, the formula needed is public function Form_YOS_Base. There are several other options as well. I thought about creating a select case statement that wrote the correct function and field names to a text box on the form, but the calculated field doesn't pull the value of the text box, much less use it for a calculation. The calculated field is simply blank.

I've been banging my head against this problem all day, so maybe I'm missing an obvious answer. Any help would be appreciated.

[June7]

You could have one function with an argument that passes the criteria. Then the function would have case statement to decide which calculation to use.

Call the function from query: MyFunction(Nz(Forms!formname!listbox,""))

Function declaration:
Function MyFunction(strFormula As String) As Double

Select Case strFormula
Case ""
MyFunction = 0
Case "NENUA"
MyFunction = 'do this calc
...

[FinChase]

You could have one function with an argument that passes the criteria. Then the function would have case statement to decide which calculation to use.

Call the function from query: MyFunction(Nz(Forms!formname!listbox,""))

Function declaration:
Function MyFunction(strFormula As String) As Double

Select Case strFormula
Case ""
MyFunction = 0
Case "NENUA"
MyFunction = 'do this calc
...

Thanks for your response! I knew there had to be a way to call the functions with a select case but couldn't figure it out.

It's giving me some errors, however. Here is the code that I wrote:

Function NGCost(strFormula As String) As Double
Select Case strFormula
    Case ""
       NGCost = 0
    Case "NENUA"
      NGCost = "Form_O65([qryNG_Post65_Plan Options]![Monthly Full Cost]-([qryNG_Post65_RetCode]![Percentage]* [qryNG_Post65_Plan Options]![Monthly Full Cost])"
    Case "NENUB"
        NGCost = "Form_O65([qryNG_Post65_Plan Options]![Monthly Full Cost]-([qryNG_Post65_RetCode]![Percentage]* [qryNG_Post65_Plan Options]![Monthly Full Cost])"
    Case "NENUC"
        NGCost = "Form_O65([qryNG_Post65_Plan Options]![Monthly Full Cost]-([qryNG_Post65_RetCode]![Percentage]* [qryNG_Post65_Plan Options]![Monthly Full Cost])"
    Case "NYNUNO"
        NGCost = "Form_O65([qryNG_Post65_Plan Options]![Monthly Full Cost]-([qryNG_Post65_RetCode]![Percentage]* [qryNG_Post65_Plan Options]![Monthly Full Cost])"
    Case "NYNUNN"
        NGCost = "Form_O65([qryNG_Post65_Plan Options]![Monthly Full Cost])"
    Case Else
        NGCost = "Form_YOS_Base([qryNG_Post65_Plan Options]![Monthly Full Cost]*[qryNG_Post65_RetCode]![Percentage])"
End Select
End Function

I'm calling it in my query with the following: Calculated Cost: NGCost(Nz([Forms]![frmNG Retiree Options]![lstNG_RetGrp]))

It gives me a Type Mismatch error, and says the expression is typed incorrectly or is too complex to be evaluated. The formulas in the individual functions work when I enter them directly, so I think it's capable of the math.

[June7]

For one thing, the Nz is incomplete. Need the alternate value so that if field is null, the function won't bug. String variable can't handle a null. An alternative is to declare the variable as Variant and then it can accept anything.
Nz([Forms]![frmNG Retiree Options]![lstNG_RetGrp],0)

Remove the quote marks surrounding the function calls.

Are all these functions behind the form or in a general module?

The calcs probably error without the Forms! prefix.

If you are doing the complete calculation with the shown expressions, there is no need to wrap the calc expressions in a function call. What you have is calls to 2 other functions with an argument that is a calculated value. Consider:

Select Case strFormula 
Case ""
  NGCost = 0
Case "NENUA", "NENUB", "NENUC", "NYNUNO"
  NGCost = Forms![qryNG_Post65_Plan Options]![Monthly Full Cost] - Forms![qryNG_Post65_RetCode]![Percentage] * Forms![qryNG_Post65_Plan Options]![Monthly Full Cost]
Case "NYNUNN"
  NGCost = Forms![qryNG_Post65_Plan Options]![Monthly Full Cost]
Case Else
  NGCost = Forms![qryNG_Post65_Plan Options]![Monthly Full Cost] * Forms![qryNG_Post65_RetCode]![Percentage]
End Select

Your first forumla has an unpaired paren. Which should happen first - the subtraction or the multiplication? If you want to subtract first, need parens around those two terms.

[FinChase]

For one thing, the Nz is incomplete. Need the alternate value so that if field is null, the function won't bug. String variable can't handle a null. An alternative is to declare the variable as Variant and then it can accept anything.
Nz([Forms]![frmNG Retiree Options]![lstNG_RetGrp],0)

Remove the quote marks surrounding the function calls.

Are all these functions behind the form or in a general module?

The calcs probably error without the Forms! prefix.

If you are doing the complete calculation with the shown expressions, there is no need to wrap the calc expressions in a function call. What you have is calls to 2 other functions with an argument that is a calculated value. Consider:

Select Case strFormula 
Case ""
 NGCost = 0
Case "NENUA", "NENUB", "NENUC", "NYNUNO"
 NGCost = Forms![qryNG_Post65_Plan Options]![Monthly Full Cost] - Forms![qryNG_Post65_RetCode]![Percentage] * Forms![qryNG_Post65_Plan Options]![Monthly Full Cost]
Case "NYNUNN"
 NGCost = Forms![qryNG_Post65_Plan Options]![Monthly Full Cost]
Case Else
 NGCost = Forms![qryNG_Post65_Plan Options]![Monthly Full Cost] * Forms![qryNG_Post65_RetCode]![Percentage]
End Select

Your first forumla has an unpaired paren. Which should happen first - the subtraction or the multiplication? If you want to subtract first, need parens around those two terms.

Sorry to be so stupid here, but should Forms! be used when it is referencing a query. I tried it different ways, but the error message I'm getting now is that this query has not been externally defined.

In answer to your question, these functions are in a separate code module. I do have a form that users input the variable information, so I could set the Select Case statement up there if you think it will run better.

Good catch on the missing parens. I had it correctly in the function I created, but not here.

[June7]

I assumed was name of form, in spite of the qry prefix. Can't grab data from a table/query this way in code. For one thing, doesn't specify what record to pull the data from. Must use domain aggregate functions (DLookup, DSum, DAvg DMax, DMin) or open a recordset or refer to open form or report.

What calcs take place in the individual functions?

You could pass [Monthly Full Cost] and [Percentage] as two more arguments of the NGCost function.