Can a calculated field in a table/query be made to reference a public VBA routine or Variable?
Thanks in advance
Can a calculated field in a table/query be made to reference a public VBA routine or Variable?
Thanks in advance
A query can certainly reference a public function, not a variable. Don't use calculated fields in tables, so not sure about that.
Paul, thank you for your prompt response. I haven't used calculated fields yet so my question is pretty basic. I don't anticipate creating a calculated field using VB, but I would like to add a calculated field to a table and have the expression reference a VB function. Imagine I have a table MyTable which has 3 numeric fields X, Y and Z and imagine that in Module1 I have a routine
Public Function DistanceCalc(Xpos, Ypos, Zpos)
DistanceCalc = Sqr(Xpos ^ 2 + Ypos ^ 2 + Zpos ^ 2)
End Function
Now I want to create a new calculated field "Distance" into MyTable. I create a new field called Distance, select type Calculated, and then in the expression box I write
DistanceCalc([X],[Y],[Z])
when I try to look at the table in the datasheet view I get the error msg
the expression DistanceCalc([X],[Y],[Z]) cannot be used in a calculated column
(I realize in this case I could probably write the desired expression directly into the field instead of trying to reference a VBA function, but I have created this example for simplicity. I am trying to created a calculated field which would involve complex calculations and if statements which are beyond what I could directly insert into the expression field, hence the need to reference a function created in VB.)
Can anyone tell me what I should insert into the calculation field to get it to reference the VB function DistanceCalc (if that's possible) ?
Thanks in advance.
According to MS:
Be aware that calculated fields cannot call user-defined functions, only built-in functions.
That's not what I wanted to hear but thanks for saving me a lot of time.
No problem...sorry.
Don't do this in a table. You can call a user defined function from a calculated field in a query.