CODEWITHSUNDEEP
ms-accessms-access-2007vba
Patrick
Patrick

Reputation: 113

DCount with 2 criteria

I am using DCount to help display an error message if the data signals an error. I have it working with one criteria where a number is equal to another number. Now I wanted to add another criteria in there where another field, ReturnDate (this field is Text not Date/Time) is equal to a hyphen (-). I'm just not really sure how to format it. I have this:

If DCount("*", "CrewTable", "KitNumber=" & _
  Me.AssignKit.Value And "ReturnDate=" & _
  "-") > 0 Then
  Cancel = True
  MsgBox "Kit is already assigned!"
  AssignKit = ""
  AssignKit.SetFocus
Else
...

The error pops up with a 'Type Mistmatch' and the debugger highlights the whole statment from 'If -> Then' and has an error pointing to the line with the hyphen in the quotes.

Upvotes: 1

Views: 12593

Answers (3)

HansUp
HansUp

Reputation: 97101

It's easier to troubleshoot DCount errors when you store its Criteria option in a string variable.

Dim strCriteria As String
strCriteria = "ReturnDate='-' AND KitNumber=" & Me.AssignKit.Value
Debug.Print strCriteria
If DCount("*", "CrewTable", strCriteria) > 0 Then

If you had used this approach, Access would have alerted you to the fact that the original code which built the Criteria string was invalid. That should make it clearer that the problem wasn't due to the If condition, and it wasn't exactly a DCount problem either ... it was a problem with string concatenation.

Upvotes: 2

mwolfe02
mwolfe02

Reputation: 24207

If DCount("*", "CrewTable", "ReturnDate='-' AND KitNumber=" & _
          Me.AssignKit.Value) > 0 Then

Upvotes: 4

basdwarf
basdwarf

Reputation: 422

Me.AssignKit.Value & " And ReturnDate=" & _

Upvotes: 0

Related Questions