如何自定义函数,就像在SQL语句中使用的tan()、trim()等函数一样可以包含在SQL语句中的函数。而不是模块定义的那种函数。
有一个角度的数据格式是度分秒的,从一个表更新到另一个表,需要转换成弧度格式,那就要用到转换函数,函数是在模块中定义的,用一个更新查询是完成不了的。效率很低,请问有没有效率高的办法。
Public Sub ¼ì²é·½Î»¶ÁÈ¡()
Dim DbTemp As DAO.Database
Dim Rst01 As DAO.Recordset2
Dim Sql As String
Dim tempFW As Double
Dim tempID As Long
On Error Resume Next
Call ¶ÁÈ¡µ±Ç°²ÎÊý
If XmLx = "¶þά" Then
Sql = "UPDATE (((²âÏßÉè¼ÆÊý¾Ý AS a LEFT JOIN Éè¼ÆÎïÀíµãÁбí AS d ON a.ÖØÐÂÉè¼ÆID = d.ID) LEFT JOIN ÏîÄ¿ AS e ON a.ÏîÄ¿ID = e.ID) "
Sql = Sql & "LEFT JOIN [Ïß(Êø)-ÎïÀíµã¹Øϵ±í] AS f ON d.¹Øϵ±íID = f.ID) LEFT JOIN ²âÏßÉè¼Æ²ÎÊý AS g ON f.Éè¼Æ²ÎÊýID = g.ID "
Sql = Sql & "SET a.¼ì²é·½Î»=g.²âÏß·½Î» "
Sql = Sql & "WHERE e.µ±Ç°Ñ¡Ôñ<>False"
DoCmd.RunSQL Sql
Else
Sql = "UPDATE ²âÏßÉè¼ÆÊý¾Ý AS a LEFT JOIN ÏîÄ¿ AS e ON a.ÏîÄ¿ID = e.ID SET a.¼ì²é·½Î» = " & str(PYFfw) & " WHERE e.µ±Ç°Ñ¡Ôñ<>False"
DoCmd.RunSQL Sql
End If
Set DbTemp = CurrentDb
Sql = "SELECT b.ID, b.¼ì²é·½Î» FROM ÏîÄ¿ AS a INNER JOIN ²âÏßÉè¼ÆÊý¾Ý AS b ON a.ID = b.ÏîÄ¿ID where a.µ±Ç°Ñ¡Ôñ"
Set Rst01 = DbTemp.OpenRecordset(Sql)
Rst01.MoveFirst
Do While Not Rst01.EOF
tempID = Rst01.Fields("ID").Value: tempFW = Rst01.Fields("¼ì²é·½Î»").Value
tempFW = JtoH(tempFW)
Sql = "update ²âÏßÉè¼ÆÊý¾Ý AS a SET a.¼ì²é·½Î» = " & str(tempFW) & " where a.ID=" & str(tempID)
DoCmd.RunSQL Sql
Rst01.MoveNext
Loop
Rst01.Close
DbTemp.Close
End Sub