DCOUNT WITH MULTIPLE CRITERIA

Hi Guys,

I'm new to Microsoft access and am rock stuck at a formula. Would really appreciate your help.

=DCount("ACTIONS.[ID]","[ACTIONS]","[ACTIONS].T1000ID = " & [ID] AND [ACTIONS].[% COMPLETE] > 0.6)
I have also tried =DCount("ACTIONS.[ID]","[ACTIONS]","[ACTIONS].T1000ID = " & [ID] AND "[ACTIONS].[% COMPLETE] > 0.6") however I do not get the results I am looking for.

Basically I would like a count of ACTION IDs for two AND Conditions
1. The T1000ID is equal to ID
2. % Completion is > 0.6

Any assistance is really appreciated. This is driving me crazy

Thanks

Hamza
Hamza NadeemAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
=DCount("ACTIONS.[ID]","[ACTIONS]","T1000ID = " & [ID] & " AND [% COMPLETE] > 0.6")

or

=DCount("ACTIONS.[ID]","[ACTIONS]", "[% COMPLETE] > 0.6  And T1000ID = " & [ID] )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamza NadeemAuthor Commented:
Thank You so much Roy...that really helps.
Would you be able to recommend a resource (website/youtube) or book where I can learn the Syntax to avoid having problems in the future?

Thanks
0
Dale FyeCommented:
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)

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)

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)"

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
0
Rey Obrero (Capricorn1)Commented:
@HAMZA

see this link for VBA tutorials  http://www.functionx.com/vbaccess/
0
Rey Obrero (Capricorn1)Commented:
solution was provided
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.