Results 1 to 4 of 4

Thread: Count Days

  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Talking Unanswered: Count Days

    Can anybody help me out with this?

    ID Date Category
    1. 4/10/2006 1
    2. 4/10/2006 1
    3. 4/10/2006 1
    4. 4/23/2006 1
    5. 4/23/2006 1
    6. 4/28/2006 1
    7. 4/28/2006 1

    How can I count only days?
    For Example to show: 3 days
    I tried this:
    Me.Days = DCount("Date", "Records", "Category = 1 And DatePart('yyyy', [Date])=" & Me.txtYear) but it counts all records.
    It shows 7 days

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You'd probably be better off creating a query to pull this for you:

    SELECT DISTINCT COUNT(yourDate)
    FROM yourTable
    WHERE [Date] = variableGoesHere
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Talking

    There is another way using the query builder in design mode, however you do need two queries to be able to do this.

    qry 1 would have the dates listed then select the sigma (a backwards E) button at the top, this will give you some options, select "Group By"

    This will then filter the dates and show only unique entries.

    qry 2, import the qry above into qry 2, add the date field from this query and again select the sigma and this time, select "Count". This will count your grouped by dates!

    Hope this helps;

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Teddy
    SELECT DISTINCT COUNT(yourDate)
    FROM yourTable
    WHERE [Date] = variableGoesHere
    without a GROUP BY, a COUNT query will return only one row

    so DISTINCT is redundant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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