How to group record based on specific date range and calculate totals!

[Hellgofi]

Hi,

This is my first message. Here is my question:
I have one table named Expenses. There are 3 fields in that table, namely, Expense_ No, Amount and Date. There are many records in that table that show the expenses made throughout the year.

I would like to group those expenses according to specific date range: (from 15th of each month to 14th of next month) and would like to calculate totals for each date range.

I would like to list expense records like below for example:

January 15- February 14
expense record 1 detais
expense record 2 details
.....
Total =XXX

February 15-March 14
expense record 3 details
expense record 4 details....
...
Total =YYY
..
..
Grand Total=ZZZ

How can I do this? I need a code sample if possible 'cause I am not experienced in coding.
Thanks in advance
regards

 

[Jon K]

I have attached a sample database, which contains a query and a report based on the query.
.

 

[Hellgofi]

Thanks a lot Jon, you made my day I appreciate your time, your help for building the sample. Thanks thanks thanks.

But I wonder whether or not that can be achieved by using Day(), Month() or another command and without using another table to hold date ranges. Date range is always be one of these:
Montly , weekly, from 15th of any month to 14th of next month.

regards

 

[Jon K]

Since having months of 15th to 14th is equivalent to adding "1 month less 14 days" to the original dates, we can use the DateAdd() function to arrive at the related dates:-

RelatedDate: DateAdd("m",1,DateAdd("d",-14,[DateField]))

and from the related dates we can work out the relevant date ranges the original dates are in. See the query in the database.
.

 

[Hellgofi]

this is outstanding I need to learn a lot about date related functions.
thanks a lot man

 

[Hellgofi]

I found the following page on Microsoft site. I should do more searches before asking questions Although some kind guys always help .


http://support.microsoft.com/kb/132101/EN-US/

 

[EMP]

It seems the methods in the Microsoft article return the fiscal year and fiscal month as two numbers. I think you will have a lot of work to do before you can sort and group the expenses under the text headings of

January 15-February 14
February 15-March 14
etc.

as described in your first post.

 

[Hellgofi]

Yes, It requires some extra work. Actually Jon K's first sample was enough for me (thanks again) and 2nd sample gave me another sight. Microsoft KB page includes a function which is quite useful according to my needs . Some people working with accounting databases may find it useful too. This is why I put the link here.

regards

 

[Hellgofi]

@Jon K

The report in the first sample was well organized. Sorry to bother you but how can I prepare the same thing as a form? For example when user choose the daterange from the combo box, records belong to that range appear on the form with their total.

I know that this is a form question but I couldnt pm you or send an email.

regards

 

[Jon K]

I have added a parameter query, a subform and a main form in my first sample database.

You can open the main form and select a date range from the combo box to display the expenses on the subform. The code is in the After Update event of the combo box.

The total amount displayed on the main form is read from an unbound text box in the subform footer.
.

 

[Hellgofi]

Thank you very much Jon K. I wanna hug you