Extracting field name from argument...

[cheuschober]

So this may seem like a bit of an odd duck question but I've about 20 or so fields in a certain query that all require a certain set of calculations (all of which are related).

What I'd like to is extract the field name of one of my arguments as a string and use it as a criterion for how the function operates instead of creation 20 different functions that all do nearly the same thing.

I'm not completely sure that access could even /do/ this but thought I'd try.

Ex: Func([Start_After_Days])

Public Function Func(Arg1 As Integer) as Date

Dim strArgName As string

???

Where 'msgbox strArgName' would return Start_After_Days

Any thoughts? I can't use .Name (at least I don't think I can--might be misusing it, I guess). I know I could recordset it using the same query this function will be used in as a sql statement and retrieve it that way, but that seems like the long way around and frankly a bit resource expensive (as the number of calculations that need to be done grow quite large near the end of this set of functions).

Thanks in advance,
~Chad (Vb wannabee)

Edit: Just ran across something mentioning Get Property() Anyone know if this is a false lead?

 

[MarkK]

Not exactly sure that I get what you want to do, but...
In a Query you could specify the field name directly as a string to pass into a function...
Ex: Func([StartAfterDays], "StartAfterDays")

Public Function Func(Arg1 As Integer, Arg2 As String) as Date
  Select Case Arg2
    Case "StartAfterDays"
      'Process StartAfterDays here and return a value for Func
    Case "StartBeforeDays"
      'Process StartBeforeDays here and ...
  End Select
End Function

Is this heading in a useful direction?

 

[Cosmos75]

Query Field - Pass Name & Value to Function

I am interested in this too! Did a search on Google and it actually found this thread.

Currently I have a field that stores a string that represents a formula. The formula will contain names of fields in a table. In order to calculate a value in my queries, I use the string and the values of the fields for the calculated field.

The following is code that I am using to do this.

Public Function getFormulaResult(strFormula As String, _
                                 x As Double, y As Double, _
                                 z As Double) As Double

Do While InStr(strFormula, "x") > 0
    strFormula = Left(strFormula, InStr(strFormula, "x") - 1) & _
    Trim(str(x)) & Mid(strFormula, InStr(strFormula, "x") + Len("x"))
Loop

Do While InStr(strFormula, "y") > 0
    strFormula = Left(strFormula, InStr(strFormula, "y") - 1) & _
    Trim(str(y)) & Mid(strFormula, InStr(strFormula, "y") + Len("y"))
Loop

Do While InStr(strFormula, "z") > 0
    strFormula = Left(strFormula, InStr(strFormula, "z") - 1) & _
    Trim(str(z)) & Mid(strFormula, InStr(strFormula, "z") + Len("z"))
Loop

getFormulaResult = Eval(strFormula)
[color=green]'e.g. getFormulaResult("(2*x)+y+z",2,4,8) = 16[/color]

End Function

This works great for a three variable formula, but if I have 5 or 6, I need to add more Do ... Loops. In my query, I have all the variables, whether or not there are available in the formula.

If it is possible to somehow pass the value and the name of a field at the same time, I was thinking of using using ParamArray to simplify the function.

The closest I've been able to come to something like this is by using a two dimensional array.

Public Function getFormulaResultDynamic(strFormula As String, _
                                        ParamArray PassArray() As Variant) _
                                        As Double

Dim intVariables As Integer
intVariables = (UBound(PassArray) + 1) / 2

ReDim ArgArray(intVariables - 1, 2)

Dim i As Integer
Dim iArg As Integer
iArg = -1
For i = LBound(PassArray) To UBound(PassArray)
    If i Mod 2 = 0 Then
        iArg = iArg + 1
        'Value
        ArgArray(iArg, 1) = PassArray(i)
        Debug.Print "ArgArray(" & iArg & ", 1) = " & ArgArray(iArg, 1)
    Else
        'Field Name
        ArgArray(iArg, 2) = PassArray(i)
        Debug.Print "ArgArray(" & iArg & ", 2) = " & ArgArray(iArg, 2)
    End If
Next i

Dim dlbField As Double
Dim strField As String

For i = LBound(ArgArray) To UBound(ArgArray)
    dlbField = ArgArray(i, 1)
    strField = ArgArray(i, 2)
    Debug.Print "dlbField = " & dlbField
    Debug.Print "strField = " & strField
    Do While InStr(strFormula, strField) > 0
        strFormula = Left(strFormula, InStr(strFormula, strField) - 1) & _
                     Trim(Str(dlbField)) & _
                     Mid(strFormula, InStr(strFormula, strField) + _
                     Len(strField))
        Debug.Print "strFormula = " & strFormula
    Loop
Next i

getFormulaResultDynamic = Nz(Eval(strFormula), 0)

'EXAMPLE:
'getFormulaResultDynamic("x+y+z",1,"x",2,"y",3,"z") = 6
'getFormulaResultDynamic("x+z",1,"x",2,"y",3,"z") = 4

End Function

To use this code I need to make sure the following are true

• Ensure that I have bracketed the formulas properly and uses valid VBA notation (e.g. -,+,*,/,^).
  e.g. x = 2, y = 3, z= 4
  (2*x)+(y/x) = (2*2)+(3/2)
  Log(x)/(y^x) = 0.693147180559945/81 (Log() in VBA is logarithm to the base e)
  2x+y/z = 22+3/4
  x^(y/x) <> x^y/x
• Must pass the field value first, and then the field name
• Cannot pass a string to the array (PassArray) that isn't found in the formula

 

[tkpstock]

CurrentDB.QueryDefs("MyQuery").Fields(0).Name
CurrentDB.QueryDefs("MyQuery").Fields.Count

Two functions that might be helpful...