DCount Multiple Criteria

[padlocked17]

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:

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]

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]

Awesome. Sample 1 did the trick.

That escaping and what not really confuses me.

Thanks.

 

[pbaldy]

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]

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

=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]

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

 

[Sgt Bilkp]

You make it so easy. Cheers mate!

 

[padlocked17]

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

 

[Harris21]

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!

=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!

=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]

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]

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]

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]

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]

Sorry, i copyed it wrong!

It works GREAT!!!!!

Thanks alot!!

 

[StanJx]

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.

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]

Start with the link in post 4.

 

[kieuanna96]

Awesome. Sample 1 did the trick.

 

[pbaldy]

Glad it helped.

 

[murray83]

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

=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]

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