Dcount函数

Question

我想计算一下调用号，Datecall，[Username]是表BCKHDY字段的名称，但是为什么numbercall总是等于0。如果我删除了AND DateCall= #" & DateFrom & "#，代码就会运行，这意味着Datecall有问题。出什么事了？

Private Sub txtnbCall_Click()
    Dim mydept As Integer

DateFrom = Me.txtfrom.Value
User = Forms![Navigation form]![txtLogin].Value
    If Not IsNull(DLookup("Deptname", "tblUser", "UserLogin = '" & User & "'")) Then
    mydept = DLookup("Deptname", "tblUser", "UserLogin = '" & User & "'")
    Me.txtnbCall = numbercall(mydept, DateFrom)
    End If
End Sub


Public Function numbercall(ByVal mydept As Integer, _
                            ByVal DateFrom As Date) As Integer
    numbercall = DCount("CompanyName", "BCKHDY", _
                  "[UserName] = " & mydept & "AND DateCall >= #" & DateFrom & "#")
End Function

Answer 1

这里缺少一个空格：

mydept & "AND 

应该是

mydept & " AND

只有字符串中的空格才会计算在内。如果您忘记了空格，则条件将包括类似于1And的内容

您还需要将日期格式化为yyyy-MM-dd或MM/dd/yyyy：

"[UserName] = " & mydept & " AND DateCall >= #" & Format(DateFrom, "yyyy-MM-dd") & "#")

Answer 2

您不必调用DLookup两次，一定要声明所有变量，而且您可能在DCount中过滤了错误的字段：

Private Sub txtnbCall_Click()

    Dim mydept    As Variant
    Dim DateFrom  As Date
    Dim User      As String

    DateFrom = Me!txtfrom.Value
    User = Forms![Navigation form]![txtLogin].Value

    mydept = DLookup("Deptname", "tblUser", "UserLogin = '" & User & "'")       
    If Not IsNull(mydept) Then
        Me!txtnbCall.Value = numbercall(mydept, DateFrom)
    End If

End Sub


Public Function numbercall(ByVal mydept As Integer, _
                            ByVal DateFrom As Date) As Integer

    numbercall = DCount("*", "BCKHDY", _
                  "[Deptname] = " & mydept & " AND DateCall >= #" & Format(DateFrom, "yyyy\/mm\/dd") & "#")

End Function