DCount Multiple Criteria

[StanJx]

Hi, I know this is an old thread but I hope someone will post an answer to my query. I have a table tblRequest where I have 3 fields RequiredDate VehicleNo & TeamSplit. I need to get a count in my forms code of to the selected RequiredDate and selected VehicleNo how much is the count of TeamSplit. Here is my code. I am getting a type mismatch

Dim strVehicleNo As String
Dim lCount As Long

strVehicleNo = Me.txtAssinged.Value
lCount = DCount("TeamSplit", "tblRequest", "RequiredDate=" & Me.txtReqDate And "VehicleNo=" & Chr(34) & strVehicleNo & Chr(34) And "TeamSplit=True")

If lCount = 1 Then

CurrentDb.Execute "UPDATE tblRequest SET TeamSplit = False WHERE RequiredDate= " & Me.txtReqDate & "VehicleNo= '" & Me.txtAssinged & "'"

End If

 

[dhookom]

I expect you need to delimit the dates with # and re-organize your quotes etc. Try something like:

Dim strVehicleNo As String
Dim lCount As Long
Dim strWhere As String
Dim strSQL as String

strVehicleNo = Me.txtAssinged.Value

strWhere = "RequiredDate=#" & Me.txtReqDate "# And VehicleNo=" & _
     Chr(34) & strVehicleNo & Chr(34) & " And TeamSplit=True"

lCount = DCount("TeamSplit", "tblRequest",strWhere )

If lCount = 1 Then
    strSQL = "UPDATE tblRequest SET TeamSplit = False WHERE RequiredDate= #" & _
        Me.txtReqDate & "# AND VehicleNo= '" & Me.txtAssinged & "'"
    CurrentDb.Execute strSQL
End If

Duane
Hook'D on Access
MS Access MVP

 

[StanJx]

Thanks you.

 

[Andrzejek]

StanJx .
You placed a few posts, and I hope you got some good answers.
It is customary on TT to award a star for a helpful post.
Please, click on [blue]Great Post![/blue] link on post that was the most useful.


Have fun.

---- Andy

There is a great need for a sarcasm font.