DCount Multiple Criteria (1 Viewer)

padlocked17 · Jan 18, 2008

I need to figure out how to use multiple criteria on a DCount function to check two colums based on a query.

The function is your typical:

Code:

If DCount("MemberID", "tblEnrollment", _
            "MemberID='" & MemberID & "'" )

I need to set in the Criteria basically:

Form Control = MemberID (In the Table) AND Graduated = No (Column in the table).

How would I construct that?

Also, what's the purpose of the "Expression: in the DCount function?

CraigDolphin · Jan 18, 2008

Assuming memberid is numeric and graduated is yes/no

If DCount("MemberID", "tblEnrollment","[MemberID]=" & MemberID & " AND [Graduated]=0")

Assuming memberid and graduated are both text

If DCount("MemberID", "tblEnrollment","[MemberID]='" & MemberID & "' AND [Graduated]='No'")

padlocked17 · Jan 18, 2008

Awesome. Sample 1 did the trick.

That escaping and what not really confuses me.

Thanks.

pbaldy · Jan 18, 2008

Here's a handy reference on the syntax of these:

http://www.mvps.org/access/general/gen0018.htm

At the bottom is a multi-criteria sample.

Sgt Bilkp · Jan 24, 2008

Can i hijack onto the end of this? I have tried using the reference material in the post above but not getting any luck. I have the following DCount

Code:

=DCount("[ISSI]","tblMain_data","Servicability = 3")

And i want to have a further condition, where the value in txtType =1 but i seem to be having to many " or not enough?

padlocked17 · Jan 24, 2008

Code:

=DCount("[ISSI]","tblMain_data","Servicability = 3 AND txtType = 1")

Sgt Bilkp · Jan 24, 2008

You make it so easy. Cheers mate!

padlocked17 · Jan 24, 2008

I was struggling with one too for some reason. Glad to help.

Harris21 · Feb 6, 2008

Additional Help

Hello,
I am having a few problems regarding this, i want to count records in a query that Request type is equal to IX and Request Received is > 30/03/2007 and <01/05/2007.

I used the below code but to no avail. When Run the value that is given is always 0!

Code:

=DCount("[CGT Ref]","Q00022 - Average Service Requests","[Request Type] = 'IX' AND [Request Received] < 01/01/2008 AND [Request Received] >30/03/2007")

I have also tryed this below but it returns the total amount of records!

Code:

=DCount("[CGT Ref]","Q00022 - Average Service Requests","[Request Type] = 'IX'" And " & [Request Received] Between #01/04/2007# And #01/05/2007#")

Can anyone help me out? it is probably something simple but i've been staring at it for a while and i've ran out of ideas!

pbaldy · Feb 6, 2008

Try

=DCount("[CGT Ref]","[Q00022 - Average Service Requests]","[Request Type] = 'IX' And [Request Received] Between #01/04/2007# And #01/05/2007#")

I would avoid the use of spaces and symbols in your names.

Harris21 · Feb 6, 2008

Thank you for you reply,
but the value it returns is 0 when it should be around 46,

is there any way to say count the CGT Ref where Request type is IX that was received in the month of April 2007?

CraigDolphin · Feb 6, 2008

Looks like you're using a different date convention (British or Australasian?). Access uses dates in mm/dd/yyyy (North American) format.

=DCount("[CGT Ref]","[Q00022 - Average Service Requests]","[Request Type] = 'IX' And [Request Received] Between #04/01/2007# And #04/30/2007#")

Harris21 · Feb 6, 2008

Thanks CraigDolphin that dose make a lot of sense but unfortunately the value it's returning is still 0, would there be any other way of doing this, such as a Module?

Harris21 · Feb 6, 2008

Sorry, i copyed it wrong!

It works GREAT!!!!!

Thanks alot!!

StanJx · Sep 25, 2016

Hi, I know this is an old thread but I hope someone will post an answer to my query. I have a table tblRequest where I have 3 fields RequiredDate VehicleNo & TeamSplit. I need to get a count in my forms code of to the selected RequiredDate and selected VehicleNo how much is the count of TeamSplit. Here is my code. I am getting a type mismatch.

Code:

Dim strVehicleNo As String
        Dim lCount As Long

        strVehicleNo = Me.txtAssinged.Value
        lCount = DCount("RequiredDate", "tblRequest", "RequiredDate=" & Me.txtReqDate And "VehicleNo=" & Chr(34) & strVehicleNo & Chr(34) And "TeamSplit=True")

            If lCount = 1 Then

            CurrentDb.Execute "UPDATE tblRequest SET TeamSplit = False WHERE RequiredDate= " & Me.txtReqDate & "VehicleNo= '" & Me.txtAssinged & "'"

            End If

pbaldy · Sep 25, 2016

Start with the link in post 4.

kieuanna96 · Mar 14, 2018

Awesome. Sample 1 did the trick.

pbaldy · Mar 14, 2018

Glad it helped.

murray83 · Oct 9, 2018

OK i have read the link here

http://access.mvps.org/access/general/gen0018.htm

for multiplie criteria and i think i have it right but when it counts it isnt as you can see from the picture

here is my dcount code

Code:

=DCount("[Q1]","T_Results","[Q1] = True")+DCount("[Q2]","T_Results","[Q2] = True")+DCount("[Q3]","T_Results","[Q3] = True")+DCount("[Q4]","T_Results","[Q4] = True")+DCount("[Q5]","T_Results","[Q5] = True" & " AND [Person] = '" & [Forms]![F_Results]![txt_Person] & "'")

many thanks for any help i get

cheers

pbaldy · Oct 9, 2018

You need the person criteria added to all of them. You just added it to the last.

DCount Multiple Criteria (1 Viewer)

Registered User.

GrumpyOldMan in Training

Registered User.

Wino Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Wino Moderator

Registered User.

GrumpyOldMan in Training

Registered User.

Registered User.

Registered User.

Wino Moderator

New member

Wino Moderator

Games Collector

Attachments

Wino Moderator

Similar threads

Users who are viewing this thread