Results 1 to 3 of 3

Thread: Monthly Report

  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Monthly Report

    Hi,
    I have a database with four tables in it. Each table has a field Date which is formatted as medium date ie 27-Dec-14.
    I need to run a report where I enter the Month "Nov" and get the total number of entry's for that month out of each table's
    and display them on a form. Do I use DCount to get this.
    I'm totally confused and donít know where to start, any help would be greatly appreciated.
    Ken

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Unless you want to jump through alot of hoops, entering "Nov" as a criteria for the report is not the easiest way to do that. Dates are stored internally in Access as a number (actually with both the day as an integer and the time for that day as a decimal fraction) and the formating that you are doing is simply for display purposes. One alternative would be to create a simple form with a combo box where you select the month in text form, and then have a numeric representation of the date as a hidden value, and use that as the criteria for your report.

    The DCount() function would certainly work to count the number of entries, but you could also use a GroupBy query as the data source for the report. In either case you will need to specify a field to count. One thing to be cautious of is if the date field has a time associated with it - many designers use Now() to capture the date and time a record was entered. In that case you have to consider the time component, e.g. 11/30/2014 as a criteria will only select records where they have a mid-night component. So you may want to use a critera of ">=11/1/2014 and <12/1/2014" to get all records entered in November 2014.

    Hope this helps, and welcome to the Windows Secrets Lounge. If you have further questions feel free to post them to this thread, or if they are unrelated, to start a new thread.
    Wendell

  3. The Following User Says Thank You to WendellB For This Useful Post:

    KenGra (2014-12-29)

  4. #3
    New Lounger
    Join Date
    Dec 2014
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Wendell,
    Thankyou for your reply, I've got it working but not quite like you said. I made a new table that has the Month's in it which is linked to a combo box to select the text. I have set up four queries to get the data from the tables, and have used the selection from the combo box to input the criteria on the four queries, they filter and count the total number of records for the selected Month and list's them in a text box on my Report that is linked to the querie. It might be a long way of doing it, but it worked. Again thanks for giving me the starting point of using a combo box, I hadn't thought of that.

    Regards
    Ken

Posting Permissions

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