DCOUNT WITH MULTIPLE CRITERIA

Hamza,

What I have found to work well for newbies is to actually do this work in the immediate window to start with, before incorporating it into a query, something like:

strCriteria = "([Actions].[T100ID] = 3)"
?DCount("[Actions].[ID]", "[Actions]", strCriteria)

Select all Open in new window

If this prints out a value, then you know you have that syntax correct and can move on to the next segment:

strCriteria = "([Actions].[T100ID] = 3) AND ([Actions].[% COMPLETE] > 0.6)"
?DCount("[Actions].[ID]", "[Actions]", strCriteria)

Select all Open in new window

If that works, then you can replace the hard coded values with the name of a field

DCOUNT("[Actions].[ID]", "[Actions]", "([Actions].[T100ID] = " & [ID] & ") AND ([Actions].[% COMPLETE] > 0.6)"

Select all Open in new window

and place that in your query, or in the control Source of a control on a form.

The critical point when constructing criteria strings is that if the datatype of the field on the left of the = is a string, you must wrap the value you are comparing it to in quotes.  Ideally these would be double quotes, but because it gets confusing to use double quotes, many of us simply use single quotes.  So, lets assume that the [T100ID] field is a string.  In that case, the criteria string would look like:

strCriteria = "([Actions].[T100ID] = '3')"  
Note: notice the single quotes surrounding the '3'.  Then when you translate that to use the [ID] field, you would use:

strCriteria = "([Actions].[T100ID] = '" & [ID] & "')"
in this instance there is a singe quote 2 characters to the right of the = and another one character to the left of the ).  I like to wrap my individual criteria in ( ) because it helps me to visualize the individual parts of the logic of the criteria, plus it is easier to read.

Hope this helps.
Dale

@HAMZA

see this link for VBA tutorials  http://www.functionx.com/vbaccess/

solution was provided