Solved

Trying to count records that meet certain criteria in query designer

Posted on 2013-11-25
4
359 Views
Last Modified: 2013-11-25
I'm trying to count records where a field named [Sold Date] is not blank.

Here's my code I have in "Field" in the query designer but it isn't working...

CountSold: =(Count [ID] where [Sold Date]<>"*")

????

--Steve
0
Comment
Question by:SteveL13
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39674603
CountSold: (Select Count(*) From TableX where [Sold Date] is not Null)
0
 
LVL 13

Expert Comment

by:Surone1
ID: 39674689
isdate( [Sold Date]) should work for date fields as well
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39674763
CountSold: SUM(ABS(NZ([Sold Date],"")<> ""))

(You don't need a subquery)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39674935
if you are putting this in a control source, you would use the DCOUNT( ) domain function, something like:

Control Source: =DCOUNT("*", "yourTable", "[Sold Date] IS NOT NULL")
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

829 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