MS Access 2007 sql functions?

Question

Does MS Access 2007 support the creation of custom SQL functions? if so, where is the option on the menu?

Answer 1

If you mean, you can use custom functions (UDFs) in SQL in Access, yes you can. For example, if you want to get the median value, you can write SQL in the query design window as follows:

SELECT s.Month, 
       Sum(([SentTo])) AS [Sum Sent], 
       fMedian("Statistics","Month",[Month],"SentTo") AS [Median Sent]
FROM Statistics s
GROUP BY s.Month

Where fMedian refers to the code in the module:

Function fMedian(SQLOrTable, GroupFieldName, GroupFieldValue, MedianFieldName)
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset(SQLOrTable, dbOpenDynaset)

    If IsDate(GroupFieldValue) Then
        GroupFieldValue = "#" & GroupFieldValue & "#"
    ElseIf Not IsNumeric(GroupFieldValue) Then
        GroupFieldValue = "'" & Replace(GroupFieldValue, "'", "''") & "'"
    End If

    rs1.Filter = GroupFieldName & "=" & GroupFieldValue
    rs1.Sort = MedianFieldName

    Set rs = rs1.OpenRecordset()
    rs.Move (rs.RecordCount / 2)

    If rs.RecordCount Mod 2 = 0 Then
        varMedian1 = rs.Fields(MedianFieldName)
        rs.MoveNext
        fMedian = (varMedian1 + rs.Fields(MedianFieldName)) / 2
    Else
        fMedian = rs.Fields(MedianFieldName)
    End If

End Function

From: http://wiki.lessthandot.com/index.php/Aggregate_Median_(UDF)

Answer 2

You can create custom functions using the functions of the VBA module.

Answer 3

queries can take parameters that make them look like stored procedures. Functions can be achieved using VBA.