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

    Unanswered: Count Multiple Date Fields in SameTable

    SELECT Count(*) AS LD_RECS
    FROM tblSoCalMLS_Download
    WHERE (((tblSoCalMLS_Download.LISTDATERECEIVED) Between [Enter Start Date] And [Enter End Date]));

    When I run this query and enter 4/1/2007 AND 4/30/2007 I get one answer.

    If I enter 04/01/2007 AND 04/30/2007 I get a different answer.

    Here is how my dates are stored in a Date/Time fields.
    4/26/2007 10:10:00 AM

    Also, I wouild like to strip out ALL times in my date/time fields. How is this accomplished? I can't find a simple Date format in Access.

    There are 55,156 records in the table.

    Also, I would like to count two or three additional date fields at the same time, in the same query, using the same date parameter.

    Suggestions?

    Thanks Much . . . Rick

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Is LISTDATERECEIVED a Date field? If so I would change the SQL to:

    SELECT Count(*) AS LD_RECS
    FROM tblSoCalMLS_Download
    WHERE (((tblSoCalMLS_Download.LISTDATERECEIVED) Between CDate([Enter Start Date]) And CDate([Enter End Date])));

    Because it sounds like Access is converting LISTDATERECEIVED to a string field and comparing it to what the user enters for the dates.

    Or if you want to strip off the times (ignoring them) I would use:

    SELECT Count(*) AS LD_RECS
    FROM tblSoCalMLS_Download
    WHERE (((CDate(Format(tblSoCalMLS_Download.LISTDATERECEI VED,"m/d/yy"))) Between CDate([Enter Start Date]) And CDate([Enter End Date])));

    The idea is to donvert it to a string first (Format statement with no time) then convert it back to a date (CDate statement)

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Thanks DC . . . I'm going to try this out.

    What about the more complicated scenario of multiple Count(*) date queries from the one table using different date fields?

    Thanks . . . Rick

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

    Works! To Continue . . .

    DC . . . what does CDate mean? Is that an Access Format?

    Your first query produces the correct result. Yea!

    Next I want to be able to add two additional separate date fields to the same query.

    Then - I want to add city names to the query and have the query produce the three date field query results for each city name and then place the results in a report.

    Am I dreaming?

    Can you point me in the right direction?

    Thanks much . . . Rick

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Functions in VBA that start with C followed by a data type coerces or forces an expression to a specific data type. CDate(expression) takes an expression that isn't date but looks like a date, such as the string "05/22/07" and allows Access to treat it like an actual Date.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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