Results 1 to 7 of 7
  1. #1
    GeorgeJ is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    67

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

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



    Thanks in advance

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    A query can certainly reference a public function, not a variable. Don't use calculated fields in tables, so not sure about that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    GeorgeJ is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    67
    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.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    According to MS:

    Be aware that calculated fields cannot call user-defined functions, only built-in functions.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    GeorgeJ is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    67
    That's not what I wanted to hear but thanks for saving me a lot of time.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    No problem...sorry.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    dtours is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2015
    Location
    Guelph, ON
    Posts
    6
    Don't do this in a table. You can call a user defined function from a calculated field in a query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  2. Replies: 5
    Last Post: 04-04-2014, 03:33 PM
  3. Replies: 1
    Last Post: 03-28-2013, 07:54 AM
  4. How do I reference a Calculated Field?
    By Zerdan in forum Forms
    Replies: 7
    Last Post: 05-31-2011, 01:38 PM
  5. Replies: 2
    Last Post: 05-09-2010, 04:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums