Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Simple Count Query Fails . . .

    I can't figure out why this fails. It's built according the Access 2003 HELP file.

    The fields are set to Date/Time fields.

    Code:
    SELECT Count(OCDownloadRES_2006.LISTDATE) AS [New Listings]
    FROM OCDownloadRES_2006
    HAVING (((Count(OCDownloadRES_2006.LISTDATE))>=#1/1/2006# And (Count(OCDownloadRES_2006.LISTDATE))<=#1/31/2006#) AND ((OCDownloadRES_2006.PROPSUBTYPE)="PROPSFR"));
    Also, any way to include additional fields which will have different count results in them in the same query?

    Thanks . . . Rick

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope - it is syntactically wrong I am afraid.

    Change the having clause to a where clause.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Replace HAVING with WHERE causes the following error

    "Cannot have an aggregate fiunction in a WHERE clause count etc . . ."

    Ideas

    Rick

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I reread your query - I skimmed it earlier. What on earth is this supposed to do?
    Code:
    ...Count(OCDownloadRES_2006.LISTDATE))>=#1/1/2006#...
    Count(...) returns an integer not a date so the comparrison will not work, or even worse could work but not as you think it will.

    Perhaps outline in natural English exactly what you are trying to achieve with this query.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - I am done for the night. I will take a stab at your intention being this:
    Code:
    SELECT Count(OCDownloadRES_2006.LISTDATE) AS [New Listings] FROM OCDownloadRES_2006 WHERE OCDownloadRES_2006.LISTDATE BETWEEN #1/1/2006# And #1/31/2006# AND OCDownloadRES_2006.PROPSUBTYPE = "PROPSFR"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    And guess what . . . your "stab" was the correct stab!

    Can I add addional fields to be couted the same way in the same query?

    Rick

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Answered my own question. I CAN have additional fields.

    Thanks much pootle . . .

    Rick

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pootle wins again...
    That's 4-0!

    Well done Rick for answering (half) your own question!
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Pootle wins again...
    That's 4-0!
    Yay for me! I am great at this game.











    ... er.....remind me of the rules again....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •