Solved

DCount with multiple criteria

Posted on 2016-11-21
3
60 Views
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 "'")
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 92

Accepted Solution

by:
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'")
0
 
LVL 21

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.
0
 
LVL 34

Assisted Solution

by:Norie
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 '")
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

695 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