DCount problem

[Phil]

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 = "[Grade] = " & "[cboGrade]"
' 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
Menu"
GoTo Exit_VSelectedGrade_Click
End If

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

Thanks,

 

[SteveS]

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 = "[Grade] = " & "[cboGrade]"
' 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
Menu"
GoTo Exit_VSelectedGrade_Click
End If

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

Thanks,

Add this line after your strcriteria line:

Msgbox strcriteria


then run the code. Go ahead, I'll wait. Take your time...


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
"[Grade]"


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.

 

[Tom Lake]

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 = "[Grade] = " & "[cboGrade]"
' 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

 

[Klatuu]

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"

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

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 = "[Grade] = " & "[cboGrade]"
' 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