DCount syntax

[ppoindexter]

i am trying to add one more piece of criteria to this syntax

=DCount("fldmim_core_id","tblmimtplan","fldmim_core_id=1")

the criteria to add is the value obtained from the combo box

"Cmb_Grade" which is located on the same form as the text box

that displays the DCount value

 

[dcx693]

What field in the tblmimtplan table are you comparing the value of Cmb_Grade to? For example, in your existing Dcount, you are comparing the value of fldmim_core_id to the number 1.

 

[ppoindexter]

if i understand you right....i may be trying to do something that cant be done...

there is nothing to compare it to...i want the value selected in Cmb_Grade to be the value that is used to do the count...along with the other criteria "fldmim_core_id=2"

 

[dcx693]

i want the value selected in Cmb_Grade to be the value that is used to do the count

I'm not sure what that means.

Perhaps I'm not understanding correctly. Do you want to check the value of the fldmim_core_id field against the value that is in the Cmb_Grade field?

 

[ppoindexter]

I will try to explain better...sorry for the confusion...

i have a form that i use to add fldmimtplanid (lessons) to tblmimtplan

as i add the tplans via this form, i want to see how many tplans i have entered based on grade level and core id (wu or fl)

i have placed 2 text boxes on the form with the following

txtbox_wu Control Source
=DCount("fldmim_core_id","tblmimtplan","fldmim_core_id=1")

txtbox_flControl Source
=DCount("fldmim_core_id","tblmimtplan","fldmim_core_id=2")

this counts the total number of records for wu and fl for all grade levels...

what i need is to count the total number of records for wu 1st grade , wu 2nd grade, fl 1st grade, fl 2nd grade, etc.....

so i thought i could add another criteria to the DCount syntax criteria being "fldlevel2" (grade Level) and pass the selection from the combo box to the DCount function to show the number wu and fl for 1st grade when 1st grade has been selected in the combo box...

clear as mississippi mud???

 

[dcx693]

I'm no sure I follow, but I'll try to make some general comments.

what i need is to count the total number of records for wu 1st grade , wu 2nd grade, fl 1st grade, fl 2nd grade, etc.....

You're not trying to return all those with a single DCount are you? The D functions only return a single value.

You can run a special query called a crosstab query that will pull up the tabulated results but in a separate window. It will count the number of records for each core id and for each group level. Check out the help topic in Access on crosstab queries.

 

[ppoindexter]

this is as i suspected, i am trying to do something outside the range of DCount...i did try create the a cross tab query and placed it in the form as a subform but i couldnt figure out the links to make it work....kept getting errors so i went back to the DCount...guess i will go back to the query now i know for sure the limits of DCount...
thanks for your effort
plp

 

[WayneRyan]

plp,

The DCount can handle that, if you have two combos
grade-level and field-level:

Dim numFound As Variant

numFound = DCount("[AnyField]", _
                  "YourTable", _
                  "[Field1] = '" & Me.Combo1 & "' And " & _
                  "[Field2] = '" & Me.Combo2 & ';")
If IsNull(numFound) Then
   MsgBox("There are none")
   Exit Sub
Else
   MsgBox("There are: " & numFound)
   Exit Sub
End If

The above assumes that your fields in your table are
text, note the single-quotes. If they are numbers
omit the single-quotes.

Wayne

 

[ppoindexter]

Wayne
Thanks for the code, I am going to try it today....
regards
plp