If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dates range within Date range
I am currently trying to write an expression that flags whether a specific date range occurs within a certain date range. This will enable me to calculate the specific values associated with the that date range.
For example the data I wish to analyse is: Date Amount Return Date 21/08/1995 300 1/07/1998 12/09/1996 80 1/07/1999 4/02/1999 550 1/07/1999 21/12/1999 80 1/07/2002 29/06/2000 350 1/07/2000 6/05/2001 300 1/07/2001 23/04/2002 300 1/07/2002 25/06/2003 100 1/07/2003 and I require to know in each financial year the sum of the amounts. Ie in the financial year of 1999 (1/7/1999 to 30/6/2000) the sum of amount was 630. Can anybody help. I'm unfamilar with VBA at the moment so hope someone has a quick fix for me. |
#2
|
|||
|
|||
Dates range within Date range
Deanne,
So what you really want, is to sum(Amount) where the Return date is between two dates, is that correct? I assume you want to use the query grid, so: 1. Add your table to the query design form 2. Drag the Amount field to the first column of the grid 3. Drag the ReturnDate column to the second column of the grid. In the criteria row of the grid enter: BETWEEN #7/1/1999# AND #6/30/2000# Note that these are in US format. Although your setting may be set for DD/MM/YY, the actual data is interpreted as MM/DD/YY 4. Click on the Totals button on the menu bar (looks like an M laying on its left side) 5. Uncheck the box to include the ReturnDate field in your query. 6. Run the query. HTH Dale "Deanne" wrote in message ... I am currently trying to write an expression that flags whether a specific date range occurs within a certain date range. This will enable me to calculate the specific values associated with the that date range. For example the data I wish to analyse is: Date Amount Return Date 21/08/1995 300 1/07/1998 12/09/1996 80 1/07/1999 4/02/1999 550 1/07/1999 21/12/1999 80 1/07/2002 29/06/2000 350 1/07/2000 6/05/2001 300 1/07/2001 23/04/2002 300 1/07/2002 25/06/2003 100 1/07/2003 and I require to know in each financial year the sum of the amounts. Ie in the financial year of 1999 (1/7/1999 to 30/6/2000) the sum of amount was 630. Can anybody help. I'm unfamilar with VBA at the moment so hope someone has a quick fix for me. |
#3
|
|||
|
|||
Dates range within Date range
Thanks Dale for your response,
I still think I confused though, I still have the problem that I need to flag records within a finiancial year when the return date actually occurs after the end of the finanical year. Likewise if the return date does occur in the financial year but the start date is prior to the start of the financial year I also need a flag. Deanne. "Dale Fye" wrote: Deanne, So what you really want, is to sum(Amount) where the Return date is between two dates, is that correct? I assume you want to use the query grid, so: 1. Add your table to the query design form 2. Drag the Amount field to the first column of the grid 3. Drag the ReturnDate column to the second column of the grid. In the criteria row of the grid enter: BETWEEN #7/1/1999# AND #6/30/2000# Note that these are in US format. Although your setting may be set for DD/MM/YY, the actual data is interpreted as MM/DD/YY 4. Click on the Totals button on the menu bar (looks like an M laying on its left side) 5. Uncheck the box to include the ReturnDate field in your query. 6. Run the query. HTH Dale "Deanne" wrote in message ... I am currently trying to write an expression that flags whether a specific date range occurs within a certain date range. This will enable me to calculate the specific values associated with the that date range. For example the data I wish to analyse is: Date Amount Return Date 21/08/1995 300 1/07/1998 12/09/1996 80 1/07/1999 4/02/1999 550 1/07/1999 21/12/1999 80 1/07/2002 29/06/2000 350 1/07/2000 6/05/2001 300 1/07/2001 23/04/2002 300 1/07/2002 25/06/2003 100 1/07/2003 and I require to know in each financial year the sum of the amounts. Ie in the financial year of 1999 (1/7/1999 to 30/6/2000) the sum of amount was 630. Can anybody help. I'm unfamilar with VBA at the moment so hope someone has a quick fix for me. |
#4
|
|||
|
|||
Dates range within Date range
Deanne,
Now that you put it that way, there are actually four categories of dates that might be of interest: 1. Those that start before the FY and end during the FY 2. those that start before the FY and end after the FY 3. those that start during the FY and end during the FY 4. Those that start during the FY and end after the FY If you want to take all four of these into account, your criteria will have to look like: [ReturnDate] = [FYStartDate] AND [StartDate] = [FYEndDate] *Note that I changed the field name of the first date field [Date] in your example table. This is because Date is a reserved word in Access and should not be used as a field name. HTH Dale "Deanne" wrote in message ... Thanks Dale for your response, I still think I confused though, I still have the problem that I need to flag records within a finiancial year when the return date actually occurs after the end of the finanical year. Likewise if the return date does occur in the financial year but the start date is prior to the start of the financial year I also need a flag. Deanne. "Dale Fye" wrote: Deanne, So what you really want, is to sum(Amount) where the Return date is between two dates, is that correct? I assume you want to use the query grid, so: 1. Add your table to the query design form 2. Drag the Amount field to the first column of the grid 3. Drag the ReturnDate column to the second column of the grid. In the criteria row of the grid enter: BETWEEN #7/1/1999# AND #6/30/2000# Note that these are in US format. Although your setting may be set for DD/MM/YY, the actual data is interpreted as MM/DD/YY 4. Click on the Totals button on the menu bar (looks like an M laying on its left side) 5. Uncheck the box to include the ReturnDate field in your query. 6. Run the query. HTH Dale "Deanne" wrote in message ... I am currently trying to write an expression that flags whether a specific date range occurs within a certain date range. This will enable me to calculate the specific values associated with the that date range. For example the data I wish to analyse is: Date Amount Return Date 21/08/1995 300 1/07/1998 12/09/1996 80 1/07/1999 4/02/1999 550 1/07/1999 21/12/1999 80 1/07/2002 29/06/2000 350 1/07/2000 6/05/2001 300 1/07/2001 23/04/2002 300 1/07/2002 25/06/2003 100 1/07/2003 and I require to know in each financial year the sum of the amounts. Ie in the financial year of 1999 (1/7/1999 to 30/6/2000) the sum of amount was 630. Can anybody help. I'm unfamilar with VBA at the moment so hope someone has a quick fix for me. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using Combo Box to Select Date Range | Sahil | Running & Setting Up Queries | 2 | June 16th, 2004 01:22 PM |
Date Range Again | Dave | Worksheet Functions | 2 | September 19th, 2003 06:12 AM |