Unanswered: how to display quantity of those records are only on current month
I have a form "delivery control" with 3 fields:
2. date delivered
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.
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.
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 !!??
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.