Create a user-defined function

Create an in-line user-defined function

1. In the Database window, click Queries   under Objects, and then click New.
2. In the New Query dialog box, select Design In-Line Function.
3. Design the in-line user-defined function in the Diagram and Grid panes of the Query Designer.
4. Click Save on the toolbar.
5. In the Save As dialog box, enter a name that follows Microsoft Access object-naming rules.

   Microsoft Access adds the  in-line user-defined function to Queries under Objects in the Database window.

Create a table user-defined function

1. In the Database window, click Queries   under Objects, and then click New.
2. In the New Query dialog box, select Create Text Table-Valued Function.

   Access displays the SQL Text Editor with the following skeleton SQL syntax:

   CREATE FUNCTION "Function1"
       (
       /*
       @parameter1 datatype = default value,
       @parameter2 datatype
       */
       )
   RETURNS /* @table_variable TABLE (column1 datatype, column2 datatype) */
   AS
       BEGIN
           /* INSERT INTO @table_variable
               sql select statement */
           /* alternative sql statement or statements */
       RETURN
       END

   Write the table user-defined function by using Transact-SQL. For more information on Transact-SQL, see the SQL Server documentation.

3. In the Save As dialog box, enter a name that follows Microsoft Access object-naming rules.

   Microsoft Access adds the table user-defined function to Queries under Objects in the Database window.

Note   You can graphically edit an SQL SELECT statement in a table user-defined function by using the Query Builder.

Create a scalar user-defined function

1. In the Database window, click Queries   under Objects, and then click New.
2. In the New Query dialog box, select Create Text Scalar Function.

   Access displays the SQL text editor with the following skeleton SQL syntax.

   CREATE FUNCTION "Function1"
       (
       /*
       @parameter1 datatype = default value,
       @parameter2 datatype
       */
       )
   RETURNS /* datatype */
   AS
       BEGIN
           /* sql statement ... */
       RETURN /* value */
       END

   Write the scalar user-defined function by using Transact-SQL. For more information on Transact-SQL, see the SQL Server documentation.

3. In the Save As dialog box, enter a name that follows Microsoft Access object-naming rules.

   Microsoft Access adds the scalar user-defined function to Queries under Objects in the Database window.

Note   You can graphically edit an SQL SELECT statement in a scalar user-defined function by using the Query Builder.

Note   You cannot use a user-defined function as the record source of a data access page.