Query Count(*) failure 1

[Rick4077]

This is not producing the correct count(*) results.

It's returning 21 for all three; LISTDATE, PENDINGDATE, and CLOSEDDATE.

What am I missing here?

Thanks . . . Rick

SELECT Count(*) AS LISTDATE, Count(*) AS PENDINGDATE, Count(*) AS CLOSEDDATE
FROM OCDownloadRES
WHERE (((OCDownloadRES.PROPSUBTYPE) Like "PROPSFR") AND ((OCDownloadRES.LISTDATE) Like "2006-08*") AND ((OCDownloadRES.PENDINGDATE) Like "2006-08*") AND ((OCDownloadRES.CLOSEDDATE) Like "2006-08*"));

 

[jebry]

Hey Rick!

What is it you are trying to do? Count(*) will always return the same count no matter how many different ways you name it.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Crowley16]

select count(fieldname) from table group by fieldname

--------------------
Procrastinate Now!

 

[Golom]

First of all, this

SELECT Count(*) AS LISTDATE, Count(*) AS PENDINGDATE, Count(*) AS CLOSEDDATE

will produce three identical values and, judging by your names, they won't be the dates you are expecting.

Second, the LIKE operator works on strings, not dates. A date field doesn't contain anything such as "2006-08". It is in fact a number and you need to use functions to either convert the number to a string or to directly compare the parts (e.g. Year, Month, Day) values from a Date field.

SELECT Count(*) AS [TheCount]

FROM OCDownloadRES

WHERE PROPSUBTYPE Like "PROPSFR"
  AND Format(LISTDATE,"yyyy-mm")    = "2006-08" 
  AND Format(PENDINGDATE,"yyyy-mm") = "2006-08" 
  AND Format(CLOSEDDATE,"yyyy-mm")  = "2006-08"

Assuming that LISTDATE, PENDINGDATE and CLOSEDDATE are fields in your table.

 

[Rick4077]

Hello all . . . thanks for your direction . . .

Golom's suggestion produced the same result as TheCount; 21!

I'm looking to find the number of times a matching date, in this case 2006-08, is in EACH of the three fields.

The following produces the correct amount; 3395 for LISTDATE. It doesn't count them, I just see the total in the dynaset. I need it to just count the records. The 2008-06* works perfectly!

SELECT OCDownloadRES.LISTDATE
FROM OCDownloadRES
WHERE (((OCDownloadRES.PROPSUBTYPE) Like "PROPSFR") AND ((OCDownloadRES.LISTDATE) Like "2006-08*"));

How can I get the counts for the three fields in one query?


Thanks . . . Rick

 

[Golom]

21" is the count of the number of records where all three fields have the value "2006-08". If you want all of them in one query then it's a trifle more complex

Select TOP 1

 (Select Count() From OCDownloadRES 
  Where PROPSUBTYPE = "PROPSFR" 
    AND Format(LISTDATE,"yyyy-mm")    = "2006-08") As [ListDate Count]

,(Select Count() From OCDownloadRES 
  Where PROPSUBTYPE = "PROPSFR" 
    AND Format(PENDINGDATE,"yyyy-mm") = "2006-08") As [PendingDate Count]

,(Select Count() From OCDownloadRES 
  Where PROPSUBTYPE = "PROPSFR" 
    AND Format(CLOSEDDATE,"yyyy-mm")  = "2006-08") As [ClosingDate Count]

From OCDownloadRES

 

[Rick4077]

Golom . . .

error says "wrong number of arguments in query expression count . . ."

I'm trying to figure that one out.

Rick

 

[Golom]

Sorry ...

Change Count() to Count([red]*[/red])

 

[Rick4077]

WHOA . . . that look good!

I need to double check the results individually to be sure.

I'll get back to you.

THANKS MUCH!!!!

Rick

 

[Rick4077]

Golom . . .

Works perfectly!

The dynaset is correct.

THANKS MUCH!!!

Rick