MS Access 2007的SQL函数? [英] MS Access 2007 sql functions?
问题描述
如果是这样,菜单中的选项在哪里?
如果你的意思是你可以使用User Defined Access中的SQL中的函数(UDF),是的,你可以。例如,如果您需要中值,则可以在查询设计窗口中编写SQL,如下所示:
SELECT s.Month ,
Sum(([SentTo]))AS [已发送数量],
fMedian(统计数据,月,[月],已发送)AS [ FROM统计s
GROUP BY s.Month
其中fMedian是指模块中的代码:
$ b $ pre $ 函数fMedian(SQLOrTable,GroupFieldName,GroupFieldValue,MedianFieldName)
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset(SQLOrTable,dbOpenDynaset)
如果IsDate(GroupFieldValue)然后
GroupFieldValue =#& GroupFieldValue& #
ElseIf不是IsNumeric(GroupFieldValue)然后
GroupFieldValue ='&替换(GroupFieldValue,','')& '
End If
rs1.Filter = GroupFieldName& =& GroupFieldValue
rs1.Sort = MedianFieldName
设置rs = rs1.OpenRecordset()
rs.Move(rs.RecordCount / 2)
如果rs .RecordCount Mod 2 = 0然后
varMedian1 = rs.Fields(MedianFieldName)
rs.MoveNext
fMedian =(varMedian1 + rs.Fields(MedianFieldName))/ 2
Else
fMedian = rs.Fields(MedianFieldName)
End If
End Function
来自: http://wiki.lessthandot.com/index。 php / Aggregate_Median_(UDF)
Does MS Access 2007 support creation of user defined sql functions? if so, where is the option for it in the menu?
If you mean can you use User Defined Functions (UDF) in SQL in Access, yes you can. For example, if you wanted the median value, you might write SQL in the query design window like so:
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 code in a 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)
这篇关于MS Access 2007的SQL函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!