Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    21

    Unanswered: User-Defined Functions in MS Access?

    I am building a query, and besides various table columns, I need it to output a string whose value depends on a non-straightforward algorithm based on the values of other columns.

    If I were in SQL-Server, I would simply write and invoke a user-defined function. In MS Access, what does one do?

    As I am a relative novice in MS Access, I would be grateful for solutions which spell things out explicitly. Thank you.

  2. #2
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404

    Smile User Defined Functions

    er... you build a user defined function, and use the value it returns....

    You might have to think about what event fires the function off, as you don't have triggers as SQL Server has, but if you enter the data via a form then there are numerous events you can use.
    Last edited by Ryker; 06-03-04 at 05:50. Reason: :-)

  3. #3
    Join Date
    Apr 2003
    Posts
    21
    Quote Originally Posted by Ryker
    er... you build a user defined function, and use the value it returns....
    I do not understand. As far as I am aware, MS Access does not have user-defined functions. At least, I don't see them in the list of available object types (Tables, Queries, Forms, Reports, etc.).

  4. #4
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404

    Smile User defined functions

    If you have a form open it in design view. Look at the even properties. Click on the On load event. You should be given a choice of three things one of which is code builder. Try a simple bit of code, e.g.

    msgbox "Hi there"

    Once you've got some code it's easy to scroll up the top of the code page where you should see soemthing like

    Option compare database
    Option Explicit

    Here you can produce your function, e.g.

    Private Function todaysDate()
    todaysDate =now()
    end function

    In the on load event code you can now put

    msgbox "The date is " & todaysDate()

    Hope that helps.

  5. #5
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Side note: User defined functions can be accesses in the query builder. You first have to create and save the module with funcitons in it. once you have done that you can browse for them in the query builder.

  6. #6
    Join Date
    Apr 2003
    Posts
    21
    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •