DCount with multiple criteria

Posted on 2016-11-21
Last Modified: 2016-11-21
What is wrong with:

=DCount("[ExpiredID]","tblExpiredListings","DiffAgentDateRelisted BETWEEN #" & [Forms]![frmExpiredListingReportDateRange]![txtStartDate] & "# AND #" & [Forms]![frmExpiredListingReportDateRange]![txtEndDate] & "#" & " AND [ListingAgent] = '" John Doe "'")
Question by:SteveL13
LVL 92

Accepted Solution

Patrick Matthews earned 250 total points
ID: 41896453
Your're missing some ampersands.  If you always want it to be John Doe, why not:

=DCount("[ExpiredID]","tblExpiredListings","DiffAgentDateRelisted BETWEEN #" & [Forms]![frmExpiredListingReportDateRange]![txtStartDate] & "# AND #" & [Forms]![frmExpiredListingReportDateRange]![txtEndDate] & "#" & " AND [ListingAgent] = 'John Doe'")
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 125 total points
ID: 41896462
you can combine your literals -- you also have an extra quote mark and spaces'
=DCount("[ExpiredID]","tblExpiredListings","DiffAgentDateRelisted BETWEEN #" & [Forms]![frmExpiredListingReportDateRange].[txtStartDate] & "# AND #" & [Forms]![frmExpiredListingReportDateRange].[txtEndDate] & "# AND [ListingAgent] = 'John Doe'")

Open in new window

 I like to use . instead of ! when referring to controls.
LVL 33

Assisted Solution

Norie earned 125 total points
ID: 41896464
Try this.

=DCount("[ExpiredID]","tblExpiredListings","DiffAgentDateRelisted BETWEEN #" & [Forms]![frmExpiredListingReportDateRange]![txtStartDate] & "# AND #" & [Forms]![frmExpiredListingReportDateRange]![txtEndDate] & "# AND [ListingAgent] = 'John Doe '")

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
object variable or with block not set 6 31
Create report using crosstab query 11 29
MS Access Delete All Excel sheets 1 10
Modify report 8 10
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question