0
votes

I want to count the call number, Datecall, [Username] is the name of fields of table BCKHDY but why numbercall always equal 0. If I delete AND DateCall= #" & DateFrom & "#, code run, that mean there is something wrong with Datecall. What 's wrong?

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
2

2 Answers

1
votes

You're missing a space here:

mydept & "AND 

Should be

mydept & " AND

Only spaces inside a string count. If you forget the space, the criteria would include something like 1And

You also need to format the date as either yyyy-MM-dd or MM/dd/yyyy:

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

You don't have to call DLookup twice, do declare all variables, and you probably filter on the wrong field in 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