Single Date Into Multiple Fields in Query

[Rick4077]

I'm posting it here in case no one catches it at the end of the other thread.

I'm so close now . . . the query below produces the correct results, 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.

The date fields are formatteds thus; yyyy-mm-dd 00:00:00

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

CODE
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]));

 

[dhookom]

I don't understand "have it placed in the three date fields before the query is run." What is your table structure and what do you want to display?

I'm not sure why you are grouping by [PROPSUBTYPE]. I would think you would want to remove it from the Group By and remove the "HAVING ...." and replace it with "WHERE ..." between the "FROM ..." and "GROUP BY..." clauses.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rick4077]

The Dynaset I'm looking for is . . .

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

etc . . .

I need to select diffeent PROPSUBTYPE ie, PROPSFR and PROPCONDO. I DO NOT need to see that in the dynaset.

I don't understand the HAVING cluase!

 

[dhookom]

Try this:

SELECT OCDownloadRES.CITY, 
Count(OCDownloadRES.LISTDATE) AS CountOfLISTDATE, 
Count(OCDownloadRES.PENDINGDATE) AS CountOfPENDINGDATE,
Count(OCDownloadRES.CLOSEDDATE) AS CountOfCLOSEDDATE
FROM OCDownloadRES
WHERE PROPSUBTYPE=[ENTER PROP TYPE]
GROUP BY OCDownloadRES.CITY;

What do you want do with a user input of a date? Do you want to count the number of non-null values in each of the fields for a date range?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rick4077]

Duane . . .

I need to retieve records that match a date parameter.

"2006-08" The format of these three fields are text. I do not wish to convert them to a date field.

The same date parameter will be used by all three date fields so I need a way for the end user to insert a date, ie., 2006-08 into these three fields and then have the total dates returned for each field counted and displayed like this for as many cities as there are, usually no more than 40.

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

 

[dhookom]

You should use controls on forms for user interface.

SELECT OCDownloadRES.CITY, 
Sum(Abs(LISTDATE=Forms!frmRptSelect!txtDate)) AS CountOfLISTDATE, 
Sum(Abs(PENDINGDATE=Forms!frmRptSelect!txtDate)) AS CountOfPENDINGDATE,
Sum(Abs(CLOSEDDATE=Forms!frmRptSelect!txtDate)) AS CountOfCLOSEDDATE
FROM OCDownloadRES
WHERE PROPSUBTYPE=Forms!frmRptSelect!txtPropType
GROUP BY OCDownloadRES.CITY;

I would actually normalize your table structure but it's not my table ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rick4077]

Duane . . . where would this code go, in a command button on the form?

I have had little exposure to forms in Access 2003.

Rick

 

[dhookom]

This is a query forum and you asked a question and provided the SQL view of a query so this is the SQL view of a query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]