# DCount problem

G

#### Guest

Hi,
I have a form where the user selects a grade from a combo box (cboGrade),
selects a status using a check box (ckStatusCurrent) and then clicks on a
command button which counts the students matching the criteria selected.
[Grade] and [Status] are fields in a Students table.

If I set my stcriteria as 3 separate criterias as follows I get the
right count for each criteria

' strcriteria = "[ckStatusCurrent] = -1"
' strcriteria = "[Status] = 'Current'"

If the ckStatusCurrent box is checked and the students [Grade] matches
the [cboGrade] and the students [Status] is 'Current' I want to count
the student.

If I try to COMBINE the criteria into one to restrict using the combination
of the three I always get a DCount of zero. There are students that meet
the selected grade and have a "Current" status and the check box is
checked.

Here is my combined strcriteria:

strcriteria = "[Grade] = " & "[cboGrade]" & " & [Status] = 'Current'" _
& " & [ckStatusCurrent] = -1"

intx = DCount("[Student_Key]", "Students", strcriteria)
If (intx = 0) Then
MsgBox "No students for the grade and status selected", , "Report
End If

What am I missing????????????

Thanks,

S

#### SteveS

Phil said:
Hi,
I have a form where the user selects a grade from a combo box (cboGrade),
selects a status using a check box (ckStatusCurrent) and then clicks on a
command button which counts the students matching the criteria selected.
[Grade] and [Status] are fields in a Students table.

If I set my stcriteria as 3 separate criterias as follows I get the
right count for each criteria

' strcriteria = "[ckStatusCurrent] = -1"
' strcriteria = "[Status] = 'Current'"

If the ckStatusCurrent box is checked and the students [Grade] matches
the [cboGrade] and the students [Status] is 'Current' I want to count
the student.

If I try to COMBINE the criteria into one to restrict using the combination
of the three I always get a DCount of zero. There are students that meet
the selected grade and have a "Current" status and the check box is
checked.

Here is my combined strcriteria:

strcriteria = "[Grade] = " & "[cboGrade]" & " & [Status] = 'Current'" _
& " & [ckStatusCurrent] = -1"

intx = DCount("[Student_Key]", "Students", strcriteria)
If (intx = 0) Then
MsgBox "No students for the grade and status selected", , "Report
End If

What am I missing????????????

Thanks,

Msgbox strcriteria

OK, is that what you expected? No?

1) You can't use the ampersand to mean "And" in the criteria.

2) I think you want the *value* from the field [Grade], not the string

So the revised criteria should be (all on one line):

strcriteria = "[Grade] = '" & [cboGrade] & "' And [Status] = 'Current'"
& " And [ckStatusCurrent] = -1"

Try the message box (Msgbox) line and see if the criteria is what you
expected.

T

#### Tom Lake

Phil said:
Hi,
I have a form where the user selects a grade from a combo box (cboGrade),
selects a status using a check box (ckStatusCurrent) and then clicks on a
command button which counts the students matching the criteria selected.
[Grade] and [Status] are fields in a Students table.

If I set my stcriteria as 3 separate criterias as follows I get the
right count for each criteria

' strcriteria = "[ckStatusCurrent] = -1"
' strcriteria = "[Status] = 'Current'"

If the ckStatusCurrent box is checked and the students [Grade] matches
the [cboGrade] and the students [Status] is 'Current' I want to count
the student.

If I try to COMBINE the criteria into one to restrict using the
combination
of the three I always get a DCount of zero. There are students that meet
the selected grade and have a "Current" status and the check box is
checked.

Here is my combined strcriteria:

strcriteria = "[Grade] = " & "[cboGrade]" & " & [Status] = 'Current'"
_
& " & [ckStatusCurrent] = -1"

Don't you have to use the word And instead of the ampersand?

strcriteria = "[Grade] = " & [cboGrade] & " And [Status] = 'Current' And
[ckStatusCurrent] = -1"

Tom Lake

G

#### Guest

Yes, Tom, that is the problem. Interesting it does not return an error;
Instead, it returns 0. The & is only for string concatenation. Use the word
"and"

[ckStatusCurrent] = -1"

Tom Lake said:
Phil said:
Hi,
I have a form where the user selects a grade from a combo box (cboGrade),
selects a status using a check box (ckStatusCurrent) and then clicks on a
command button which counts the students matching the criteria selected.
[Grade] and [Status] are fields in a Students table.

If I set my stcriteria as 3 separate criterias as follows I get the
right count for each criteria

' strcriteria = "[ckStatusCurrent] = -1"
' strcriteria = "[Status] = 'Current'"

If the ckStatusCurrent box is checked and the students [Grade] matches
the [cboGrade] and the students [Status] is 'Current' I want to count
the student.

If I try to COMBINE the criteria into one to restrict using the
combination
of the three I always get a DCount of zero. There are students that meet
the selected grade and have a "Current" status and the check box is
checked.

Here is my combined strcriteria:

strcriteria = "[Grade] = " & "[cboGrade]" & " & [Status] = 'Current'"
_
& " & [ckStatusCurrent] = -1"

Don't you have to use the word And instead of the ampersand?

strcriteria = "[Grade] = " & [cboGrade] & " And [Status] = 'Current' And
[ckStatusCurrent] = -1"

Tom Lake