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.

回答1:

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

回答2:

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.

回答3:

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