时间戳

首页 > 解决方案 > 公用函数的 SQL 语句

vba - 公用函数的 SQL 语句

问题描述

我有一个查询，根据工作日为我提供当前员工率：

(Select Top 1 T1.Rate FROM tblERates as T1
WHERE T1.EMPID = tblPayroll.EMPID And T1.EffectiveDate <= tblPayroll.WorkDay
ORDER BY T1.EMPID asc, T1.EffectiveDate desc)

该项目正在增长。我需要在不同的表单/报告上返回员工率，所以我想将此 SQL 转换为公共函数。

我试过这个：

Public Function fncERate(EID As Integer, WD As Date) As Double

    Dim intERate As Double
    Dim intWD As String

    intWD = "#" & Format(WD, "m/d/yyyy") & "#"

    intERate = "SELECT TOP 1 Rate" _
                & "FROM tblERates" _
                & "WHERE EMPID = EID And EffectiveDate <= intWD" _
                & "ORDER BY EMPID asc;"
    fncERate = intERate

End Function

我收到“类型不匹配错误”。

标签： vbams-access

解决方案

经过一番修改后，我想出了这个：

Public Function fncERate(EID As Integer, WD As Date) As Double


    Dim intERate As String
    Dim intWD As String
    Dim intEID As Integer


    intWD = "#" & Format(WD, "m/d/yyyy") & "#"

    intERate = "SELECT TOP 1 [Rate]" & _
                " FROM tblERates" & _
                " WHERE [EMPID] = " & EID & " And [EffectiveDate] <= " & intWD & " " & _
                " ORDER BY [EMPID] asc;"

    With CurrentDb.OpenRecordset(intERate)
        If Not (.BOF And .EOF) Then
            fncERate = .Fields(0)
        End If
    End With


End Function

推荐阅读