Simple Query Fails

[Rick4077]

I just can't figure out why this doesn't produce any results, just 0?

SELECT OCDownloadRES.CITY, Format("LISTDATE","yyyy-mm") AS Expr1
FROM OCDownloadRES
WHERE (((Format("LISTDATE","yyyy-mm"))="2006-08") AND ((OCDownloadRES.PROPSUBTYPE)="PROPSFR"));

What I'm trying to do is get all of the sales for Aug 06 and then group (sort?) them by all cites in a report.

Thanks . . . Rick

 

[traingamer]

If LISTDATE is a field, try [LISTDATE] instead of "LISTDATE".

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Rick4077]

Error message is "missing operator" when I do this.

BTY, which occourence of "LISTDATE" should this be changed to [LISTDATE]?

Thanks . . . Rick

 

[traingamer]

Both.

 

[PHV]

SELECT CITY, Format([LISTDATE], 'yyyy-mm') AS [Month]
FROM OCDownloadRES
WHERE Format([LISTDATE], 'yyyy-mm') = '2006-08' AND PROPSUBTYPE = 'PROPSFR'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

PHV . . . knowledge is power!

How do you learn this stuff?

Now onward and forward.

Thanks everyone.

Rick

 

[Rick4077]

Okay . . . here is where I am at present. This runs but only shows the field headers, no values in the fields. I do see problems but I don't know where to go with it.

SELECT CITY, Count(OCDownloadRES.LISTDATE) AS [SUM of LISTDATE], 
Count(OCDownloadRES.PENDINGDATE) AS [SUM of PENDINGDATE], 
Count(OCDownloadRES.CLOSEDDATE) AS [SUM of CLOSEDDATE]

FROM OCDownloadRES
WHERE (((OCDownloadRES.PROPSUBTYPE)=[ENTER PROP TYPE]))
GROUP BY OCDownloadRES.CITY
HAVING ((Format("LISTDATE","yyyy-mm")=[ENTER yyyy-mm]) 
AND (Format("PENDINGDATE","yyyy-mm")=[ENTER yyyy-mm]) 
AND (Format("CLOSEDDATE","yyyy-mm")=[ENTER yyyy-mm]));

Here is what I'm trying to accomplish - with one stroke! All of this is in the same table.

Select CITY. There will be hundreds of occourences of the same city ie., SA, DP, GG etc.

SelectPROPSUBTYPE. User enters via [ENTER PROPERTY TYPE]

SELECT AND COUNT [LISTINGDATE], [PENDINGDATE] AND [CLOSEDDATE] by a date parameter that the user enters via [ENTER yyyy-mm]. ALL THREE of these fields will use the SAME DATE!

The Dynaset I'm looking for is . . .

|CITY|LISTINGDATE|PENDINGDATE|CLOSEDDATE|
| DP | 439       |  230      |   311    |
| GG | 604       |  213      |   361    | 
| SA | 434       |  120      |   191    |

etc . . .

Any way to make this possible? Inquiring minds want to know?

Thanks much . . . Rick

 

[Rick4077]

I'm so close now . . . the query below produces the correct results in the dynaset as shown in the previous message, only for the entire table!

I need to know how to allow the user to enter a date, ie. [Enter yyyy-mm] and have it placed in the three date fields before the query is run. This would clinch it.

Any suggestions . . . ? THANKS MUCH . . .Rick

SELECT OCDownloadRES.CITY, 

Count(OCDownloadRES.LISTDATE) AS CountOfLISTDATE, 
Count(OCDownloadRES.PENDINGDATE) AS CountOfPENDINGDATE,
Count(OCDownloadRES.CLOSEDDATE) AS CountOfCLOSEDDATE

FROM OCDownloadRES
GROUP BY OCDownloadRES.CITY, OCDownloadRES.PROPSUBTYPE
HAVING (((OCDownloadRES.PROPSUBTYPE)=[ENTER PROP TYPE]));