Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007

    Counting records based on specified criteria

    I have a query that calculates the date difference between an expiry date and todays date using DateDiff("d",Date(),[AccredExpiryDate]).

    I need to get a current count of the number of records in a specific range, i.e. a count of the number of records that are between 0 and 90 days, over 90 days, and less than 0 days (expired) etc.

    Any assistance would be greatly appreciated.

  2. #2
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Were you looking for a single query to list the counts for all three conditions? I don't have the answer to that.

    If you are just looking to get the count for a given condition, you would enter your condition in the 'Criteria' row (i.e. >0 and <90). Go to the 'View' menu and select 'Total'. Select 'Where' in the 'Total' row under your expression. Add a field to use a count field such as a id field. In that field, select 'Count' in the 'Total' row.

    SELECT Count(tblTest.test_id) AS CountOftest_id
    FROM tblTest
    WHERE (((DateDiff("d",Date(),[AccredExpiryDate]))>0 And (DateDiff("d",Date(),[AccredExpiryDate]))<90));

  3. #3
    Join Date
    Jun 2007
    Thanks for getting back to me. Much appreciated.

    I have come up with a workable solution using the DCount() function within my form and report.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Changing font color based on criteria
    By Pattie Weaver in forum Reports
    Replies: 5
    Last Post: 11-02-2010, 03:38 PM
  2. Counting rows
    By anishap in forum Access
    Replies: 0
    Last Post: 10-08-2008, 10:41 PM
  3. Replies: 1
    Last Post: 11-11-2006, 08:00 PM
  4. Replies: 1
    Last Post: 05-27-2006, 12:35 PM
  5. Open form based on Subform criteria
    By Suzan in forum Programming
    Replies: 0
    Last Post: 04-25-2006, 02:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums