Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


DCount Multiple Criteria

DCount Multiple Criteria

DCount Multiple Criteria

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

CODE -->

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 

RE: DCount Multiple Criteria

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

CODE --> vba

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 

Hook'D on Access
MS Access MVP

RE: DCount Multiple Criteria

Thanks you.

RE: DCount Multiple Criteria

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 Great Post! link on post that was the most useful.

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close