Missing something simple in DCount

I need help.  I am trying to use DCount.  I need to count occurances.  my
formula is 
Expr1: DCount([RETURNS_05],"Test"," > 0 And < 50").  I keep getting # Error
as my result. 

Please advise


Below is an example of my table.  The real table has 80,000 zipcodes, many
repeated.  

Zipcode      Returns
11111         23
12345         46 
11111         200
33424         20
31111          32
11111          25

I need to count how many times returns >0 and <50 occurs.  

Please advise.

0
anyoder
3/8/2007 1:54:18 AM
access.forms 6864 articles. 2 followers. Follow

6 Replies
1140 Views

Similar Articles

[PageSpeed] 52

You need:
- quotes around the field name to count;
- to specify a field name in the criteria.

Example:
    Expr1: DCount("[RETURNS_05]", "Test",
        "[RETURNS_05] > 0 And [RETURNS_05] < 50")

That example would be equivalent to:
    Expr1: DCount("*", "Test", "[RETURNS_05] Between 0 And 50")

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"anyoder" <u32331@uwe> wrote in message news:6ed9ee3f19156@uwe...
>I need help.  I am trying to use DCount.  I need to count occurances.  my
> formula is
> Expr1: DCount([RETURNS_05],"Test"," > 0 And < 50").  I keep getting # 
> Error
> as my result.
>
> Please advise
>
>
> Below is an example of my table.  The real table has 80,000 zipcodes, many
> repeated.
>
> Zipcode      Returns
> 11111         23
> 12345         46
> 11111         200
> 33424         20
> 31111          32
> 11111          25
>
> I need to count how many times returns >0 and <50 occurs.
>
> Please advise.
> 

0
Allen
3/8/2007 2:08:15 AM
anyoder wrote:
> I need help.  I am trying to use DCount.  I need to count occurances.
> my formula is
> Expr1: DCount([RETURNS_05],"Test"," > 0 And < 50").  I keep getting #
> Error as my result.
>
> Please advise
>
>
> Below is an example of my table.  The real table has 80,000 zipcodes,
> many repeated.
>
> Zipcode      Returns
> 11111         23
> 12345         46
> 11111         200
> 33424         20
> 31111          32
> 11111          25
>
> I need to count how many times returns >0 and <50 occurs.
>
> Please advise.

The first argument for DCount is a field name or expression in quotes.  You 
first argument is not in quotes and doesn't appear to be a field name either. 
Looks more like the query's name.

The second argument is the name of a query or table in quotes.  Your second 
argument "Test" doesn't appear to be either unless the name of your query is 
"Test".

The third argument is a valid SQL WHERE clause (without the word "where") that 
can be applied to the query or table.  Your third argument " > 0 And < 50" makes 
no sense. WHAT is supposed to be greater than zero and less than 50?

Making a total WAG here I suspect you need...

Expr1: DCount("*", "[RETURNS_05]","returns > 0 And returns < 50").


-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com


0
Rick
3/8/2007 2:16:00 AM
I got a number for each zipcode this time, however it is the same number for
all 80,000.  For some reason it is still not working.  

Rick Brandt wrote:
>> I need help.  I am trying to use DCount.  I need to count occurances.
>> my formula is
>[quoted text clipped - 17 lines]
>>
>> Please advise.
>
>The first argument for DCount is a field name or expression in quotes.  You 
>first argument is not in quotes and doesn't appear to be a field name either. 
>Looks more like the query's name.
>
>The second argument is the name of a query or table in quotes.  Your second 
>argument "Test" doesn't appear to be either unless the name of your query is 
>"Test".
>
>The third argument is a valid SQL WHERE clause (without the word "where") that 
>can be applied to the query or table.  Your third argument " > 0 And < 50" makes 
>no sense. WHAT is supposed to be greater than zero and less than 50?
>
>Making a total WAG here I suspect you need...
>
>Expr1: DCount("*", "[RETURNS_05]","returns > 0 And returns < 50").
>

0
anyoder
3/8/2007 2:36:10 AM
anyoder wrote:
> I got a number for each zipcode this time, however it is the same
> number for all 80,000.  For some reason it is still not working.

Your original post said nothing about a count "per zip code".  I thought you 
wanted to count the rows with returns greater than zero and less than 50.  If 
you want a count per zip code then feed your existing query into another totals 
query that groups on zip code and counts the rows between 1 and 49.  DCount() is 
not going to get you there.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
3/8/2007 2:42:44 AM
Ok if below is my Query

Zipcode      Returns
11111         23
12345         46 
11111         200
33424         20
31111          32
11111         27
31111        25

I need it to look like this:

Zipcode      <50
11111          2
12345          1
33424          1
31111          2

Would I not use a DCount?  

Rick Brandt wrote:
>> I got a number for each zipcode this time, however it is the same
>> number for all 80,000.  For some reason it is still not working.
>
>Your original post said nothing about a count "per zip code".  I thought you 
>wanted to count the rows with returns greater than zero and less than 50.  If 
>you want a count per zip code then feed your existing query into another totals 
>query that groups on zip code and counts the rows between 1 and 49.  DCount() is 
>not going to get you there.
>

0
anyoder
3/8/2007 2:58:30 AM
anyoder wrote:
> Ok if below is my Query
>
> Zipcode      Returns
> 11111         23
> 12345         46
> 11111         200
> 33424         20
> 31111          32
> 11111         27
> 31111        25
>
> I need it to look like this:
>
> Zipcode      <50
> 11111          2
> 12345          1
> 33424          1
> 31111          2
>
> Would I not use a DCount?

The domain functions incur a certain overhead per usage that makes them poor 
choices to use in queries or code loops because that overhead is repeated for 
every row in the query.  They are only used in queries when some particular 
effect makes them worth that ineeficiency.

Since a simple totals query will give you want you want there is no reason to 
use DCount().  Your second query...

SELECT ZipCode, Count(*) as CountOfReturns
FROM FirstQueryName
WHERE Returns BETWEEN 1 AND 49
GROUP BY ZipCode

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com






0
Rick
3/8/2007 3:17:00 AM
Reply:

Similar Artilces:

Missing files
Hello I accidentaly deleted some folders from my E:drive. I realised what I had done, opened the recycle bin, and restored them. All Ok except for the E:Videos folder, which did not appear in the recycle bin ! It's not a disaster, as I have the folder backed up, but I'm puzzled ? Ideas pls ? Thanks KK On 19/05/2010 15:15, KRK wrote: > Hello > > I accidentaly deleted some folders from my E:drive. I realised what I > had done, opened the recycle bin, and restored them. > > All Ok except for the E:Videos folder, which did not appea...

Outlook 2007 Missing Mail
I have an office where we are noticing that if we look in Vista Mail that MS outlook 2007 has missed many emails. Program restarts and reboots don't seem to correct the problem, and both programs are using the same IMAP settings. Is there a "helpful" little option in Outlook that prevents it from looking a all email receieved to an account? Does anyone else have the same problem? are there any similarities between the ones it misses? -- "nano" <nano@discussions.microsoft.com> wrote in message news:DC22F7E7-ACB7-4824-A0A1-3329D3C622EA@microsoft.com.....

Missing Emails #2
From time to time my users report that so-and-so sent them an email but they never got it. Last night my VP sent 2 emails from home and never got them. 1. We have Exchange 2000 server, all clients are Outlook 2. We have Symantec anti-virus for exchange server now I have checked the following: 1. in the Exchange logs [reading them in Excel] I can see the 2 messages arrive and seem to process normally -- HELO,MAIL, RCPT, DATA, QUIT are the 5 lines in the log; the datum on the HELO record says =+sccrmhc13.comcast.net. the Quit record as cs-uri-query of 240. 2. I have searched through...

Missing Folders after Sync
After connecting to the Exchange Server, several folders that were created to store messages disappeared. They are not on the server or any .pst file in my system. Any idea how I can find them and restore? Where were they located? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Marc" <mhyoung@yahoo.com> wrote in message news:8a8201c4854a$3d473f10$a601280a@phx.gbl... > After connecting to the Exchange Server, several folders > that were c...

Bin Transfer Entry window missing
I am trying to enable multiple bins in GP 9.0 in the sample company. I clicked on Multiple Bins, ran reconcile and checklinks, but I cannot find the window to do a Bin Transfer Entry (Bin to Bin Transfer). I am looking under Transactions >> Inventory, and "transaction entry" is listed, as well as "Transfer Entry", as well as several other options, however, "Bin Transfer Entry" is not listed. Does anyone know how I can go about getting that option? Thanks, KJ Enter a transfer entry from site A to site A and complete the line. -- Richard L. Whaley Author...

Missing Fonts in Word Art
When I open up a Pub 2003 document on a computer that did not create the document, the fonts that are missing on the current computer are listed as the document is opened. These missing fonts are only those used in text boxes, not the fonts used in word art. The word art just defaults to an arial font. When I open the "edit text" menu in word art, the font box is blank. Do I have a setting wrong or is this just how it is with Publisher 2003? Thanks, -- ~~~~~~~~~~~~~~~~~~ Brad Lyon bradnospam@ldgpv.com PIPster wrote: > When I open up a Pub 2003 document on a computer ...

a simple math formula
Hi, I'm new to this and have a very simple question. I have values, simple numbers representing weight in kg, that I wish to automatically convert to US pounds. The 'kg' numbers are in fields B5 - to B77 for example. I want the conversion result (simply multiplying each B cell entry by 2.2) placed in the ''cell adjacent. Thanks! Dave Horne Hi David In C5 enter =CONVERT(B5,"kg","lbm") and copy down through C6:C77 -- Regards Roger Govier "Dave Horne" <davehorne@home.nl> wrote in message news:upOf6pgUJHA.4916@TK2MSFTNGP06.p...

Missing Message Text
Yesterday, Windows Live Mail started doing something strange. I type the message, then press "send." The first lines of the message somehow are removed. The recipient sees only part of the message and only part of the message shows up in my sent messages folder. The first part is just gone. Seems to be only on messages that are replies. Any ideas on what the problem is, or how to correct it, would be appreciated. Never heard of this . . . . . are you on version 14.0.8089.0726? Regardless, try a reinstallation. "DeVere" <DeVere@discussions.microsoft...

user's messages-profile missing when connected to new domain-help
HI, Please be patient as the story is long: I migrated my users from a workgroup to a Client/Server network about a month ago. A network using Windows 2000 active Directory. All the clients workstations are Wondows 2000 professional. Before migration, I exported the emails, etc. to a .pst file and after migration imported the same file and all was fine. Last over the weekend our Active Directory server crashed by a Virus supposedly and we had to rebuil the servers, and couldn't have the users logon on to the network to the export/import scenarion again. After the server were re...

something curious when compiling
Hello. On my solution there are several project. Each project is compiled on the same directory (both for compiling and debugging. Everything works fine, till today. There are two project. For some reason I don't know, one of the projects (a specific project), doesn't "know" about the method of other library. I have the message : Could not load the file or assembly 'xxxxx.dll' or on of its dependencies. The system cannot find the file specified. Some of the 'xxxxx.dll' methods can be seen, and some cannot. On other project I can see the whole ...

Missing Sata Sources
Hi there When I click Data, Get External Data, New Database Query..., there are no data sources listed in the Database list under <New Data Source>. Even the defaults e.g. dBase Files, Microsoft Access Database, Excel Files, etc are not listed. I am using Windows XP Professional with Office 2000. How can I fix this? Thanks Michael ...

What am I missing?
Have PC. Onlyprofile on it is administrator.Me. What am I missing on System Restore. There are partitions that I do not need,or want, for System restore to monitor. But "Drive Settings" in 'System Properties/System Restore' the choices are greyed out. ie; Turn off System Restore(disable by Group Policy) and Turn off System restore on this drive in Drive Settings. I have checked in services in administratyive tools. Botj System restore and RPC and started and on auto. Maybe one could show me what I'm missing. I'm getting too old to remember this stuff...

Missing .dll on opening
Upon opening Outlook 2002 I get a message that I am missing MociExt.dll but I cannot get it to reload during any sort of fix or reinstall of Outlook. Any ideas? This started occuring after a recent update I did to Office. John, I don't believe that's a file installed by Office/Outlook. So, my guess is that you have an Outlook add-in that's causing this problem. Please do the following steps to confirm this assumption: 1. Exit Outlook. 2. Press and hold the CTRL key as you launch Outlook. 3. When prompted to start in Safe mode click Yes. Does the error occur? If not, please...

Windows XP Home Edition Config\System Missing
Hi: My 15 year-old daughter has a Gateway system that is 4 years old, running XP Home. Today she encountered a black screen on start-up that essentially states Windows XP could not start because the following file is missing or corrupt: \WINDOWS\SYSTEM 32\CONFIG. We can't find the Operating System CD for Windows XP Home, and my XP Professional OS disc I read cannot be used instead. I have a read a resolution that requires ijnsertion of the OS XP Home disc; boot from CD; Windows set-up initializes and press "R" to repair recovery console; hard drive is examined; pe...

simple question
hello, how do i reset my StatusBar data when user opens a new document (File->New) ? i tried something like this (it doenst work because status bar isnt ready yet) BOOL CGraphicalEditorDoc::OnNewDocument() { if (!CDocument::OnNewDocument()) return FALSE; ((CMainFrame*)AfxGetMainWnd())->UpdateStatusBar(BLA,BLA,BLA); return TRUE; } i mean by "status bar isnt readey yet" is it return an assortion error ...

RMA ( Credit) Missing Line Items
This is a multi-part message in MIME format. ------=_NextPart_000_0056_01C87CA9.5D5D6C20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Two RMA documents were received and then set as "Ready to Close". Both = documents show, in GP, as "History" but missing the line items. Checked the SQL tables and both RMA documents are in the "open" tables = (SVC05000 and SVC05200) and the line items are in the tables and appear = to be linked to the header. =20 How do we get the line items to appear on the RMA docu...

Scanpst missing
Outlook says there is problem with c:\users\xxx\appdata\local\microsoft\outlook\outlook.pst and to run scanpst.exe. I have Vista and after searching computer cannot find the file. Is there a way to download this file? My computer is french so I don't always knowthe translations It's there. Are you using 64bit vista? if so, office is in the 32 bit programs file. See http://www.outlook-tips.net/beginner/scanpst.htm -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook T...

Font missing
I created a document in Publisher and printed it. Opened document a week later and received 'font missing' error message. In the Format...Font dialong box the font's name is there with the word (missing) next to it. Where did it go? Are you using a font manager? Maybe the link was broken. Do you have the font somewhere else on your computer? If so install/re-install it. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "andy" <andy@discussions.microsoft.com> wrote in message news:9390E623-E0FE-498A-AA6...

IMF Console missing??
Hello i just have one question , simple i hope! I use my pc (windows xp pro SP2) with a custom console to administer my domain , exchange , dns , etc... Everything is working fine. Last month i installed on my exchange , the IMF from microsoft. When i am logging on to my exchange machine localy or through RDC i can see in exchange system manager below SMTP ,the IMF icon and i am able to change setting install filters and so on... When i open my console from my pc and use exchange system manager console (i have installed exchange management tools and sp1) i cannot see IMF icon below SMTP Vitu...

Missing Email #4
I am running a 2003 exchange server and one of my users sayes he isn't getting some mail that people say they are sending him. There is a mailbox listed in mailboxes called "Systemmailbox - {GIUD}" there are items listed in there. Could this be the missing mail? And is there anyway to see what these items are. Thanks for any help Damian It won't be in the System Mailbox. The System Mailbox contains schema information. There's generally no reason to ever need to access it unless PSS is instructing you to. As far as your user, make sure that you have message t...

Toolbars Missing, And option to Add Missing
Hi, My Problem is that my normal 'Formatting' toolbar is missing. When I right click on the 'Standard' toolbar to add/view the 'Formatting' the only options i have is: 'Task Pane' & 'Customize'. In 'Customize' the only tool bars listed is the 'Task Pane' & 'Work Sheet Menu Bar' Anyone know how to resolve my Problem... Please Pull-down View > Toolbars There should be about 15 bars listed. if not then select Customize > Toolbars and then push the reset button. -- Gary''s Student "SmeetaG"...

debugging missing images in a CTreeCtrl
Hello, I am having some difficulty trying to debug some missing images in a CTreeCtrl. Essentially, I subclass CDialog and have a CTreeCtrl and CImageList as data members. The CTreeCtrl is supposed to be an expandable tree with each item containing a standard checkbox and a 16x16 solid icon. The problem is that the icons are not showing up at all. I'm wondering where is a good point to start debugging the missing icon images? Things I have verified: 1. The image list is valid and is set as the TVSIL_NORMAL image list for the CTreeCtrl. 2. The bitmaps within the image list are valid (I o...

Why CELL("format",A1) doesn't return something like "#.##"? ...and other rants
I'm trying to compare the displayed value of two formatted cells. The idea is to detect the equality between 1.2345 and 1.23 when the format is "#.##" without resorting to VBA. This is Excel 2000, SP3. So I decided that this might work (may be wrapped): =IF(TEXT(A1,CELL("format",A1))=TEXT(B1,CELL("format",B1)),"equal","unequal") Unfortunately, CELL("format",A1) returns "F2", which is incompatible with the format string required by the TEXT() worksheet function. The above conditional actually evaluates to "equa...

Simple macro help #2
I have a workbook with two sheets in it. the workbook is used to keep track of machines in a particular location. on one sheet there is a weekly update and the second sheet is a year to date tracker. what i'm trying to do is on the weekly sheet i want to update the numbers for each location, weekly obviously. on the other sheet i want it to keep track of the yearly amount. for example on the weekly sheet i have 3 machines in memphis and 4 in los angeles. the yearly sheet will read 3 for memphis and 4 for los angeles. then at the end of week two i update the weekly sheet saying there...

Missing PB Updates?
Hi, When I check this page: http://msdn.microsoft.com/en-us/windowsembedded/ce/dd430902.aspx for PB 5.0 updates only the July monthly is listed. But I read somewhere else that there was a September update, which I actually found. Is there also an August release for PB 5.0 and is there another place where the updates are listed? Thanks, Jojje I just saw that there is a potential problem with netowrk in the September update. Is this the reason that it is not listed in the updates site. Regards, Jojje On 9 Nov, 10:16, Jojje <jojje.an...@gmail.com> wrote: > Hi,...