Dates range within Date range

[Guest]

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.

 

[Dale Fye]

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

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.

 

[Guest]

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]

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

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.