Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    133

    Unanswered: how to display quantity of those records are only on current month

    I have a form "delivery control" with 3 fields:
    1. productid
    2. date delivered
    3.deliveryquantity

    I create an unbound field in its footer and set it by =Sum([deliveryquantity]).
    And the total quantity of all products in all time is displays.

    But I want it to display quantity of those records are only on current month.
    for example: in this month, I want it to display quantity of those records are only on august. in next month, I want it to display quantity of those records are only on september.
    I post my file for you for reference. (using access 2003)
    Please help me. I need your help.
    many thanks.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2004
    Posts
    133
    I want it to display quantity of those records are only on current month and by each productID.
    thanks

  3. #3
    Join Date
    Jun 2004
    Location
    Nottinghamshire, UK
    Posts
    14
    cuongvt

    You might want to take a look at the following:

    Easily retrieve data for the Current Month or Year

    This might give you some pointers.

  4. #4
    Join Date
    May 2004
    Posts
    133
    thank you for the answer. I'll try this and reply you soon about the result.
    thank you

  5. #5
    Join Date
    May 2004
    Posts
    133
    Hi Graham T and averybody,
    I tested it (and I already tried using query by expression:>=DateAdd("m",-1,Date()). Yours and mine worked OK as query.

    But as I wrote before, I want it dispalying in my attatched form, not in query.
    Why I want using form? Because:
    I already created "remained" field in "products" table (see attached file) and by setting sumquantity control's control source to "remained" field of the table "products", the sum quantity will be write down to underlying "products" table. By that way, it will be more easy to create access report for my boss. (sorry for my bad english)
    Please help me to solve the problem. thanks in advanced.

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi
    If you already have a stored query that selects the data you want for the the required time period, then you can use the DCount domain function with the 'QueryName' as the 'TableName' in a TextBox on the form to count the records !!??

    Just a passing thought.

    MTB

  7. #7
    Join Date
    Oct 2003
    Posts
    706
    If you truly want "only the current month," then the best approach is to calculate the starting and ending dates, then provide these as parameters in your query, which uses BETWEEN in the WHERE clause. This is fast and efficient.

    You can also obtain all of the results all at once, for all months simultaneously. To do this, insert a calculated field which returns, say, the first day of the month represented by a chosen field. Then GROUP BY this field.

    Background: When GROUP BY is used, each unique value (combination of values) in the field (set of fields) specified as GROUP BY field(s) constitutes a unique "group" of records, and statistics (such as SUM, AVG, MIN, MAX, COUNT) can be calculated for each "group" simultaneously.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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