Can a calculated field in a table/query be made to reference a public VBA routine or Variable?

[GeorgeJ]

Can a calculated field in a table/query be made to reference a public VBA routine or Variable?



Thanks in advance

[pbaldy]

A query can certainly reference a public function, not a variable. Don't use calculated fields in tables, so not sure about that.

[GeorgeJ]

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.

[pbaldy]

According to MS:

Be aware that calculated fields cannot call user-defined functions, only built-in functions.

[GeorgeJ]

That's not what I wanted to hear but thanks for saving me a lot of time.

[pbaldy]

No problem...sorry.

[dtours]

Don't do this in a table. You can call a user defined function from a calculated field in a query.