Financial Year Reporting

[Andrew Glennie]

Hi All,

I am working on a report for which I need the result from the beginning of
the Financial Year (in my case July 01) to the previous month. I have a query
using the following to generate the range - unfortunately it produces a range
including the current month.

Between
DateSerial(Year(Date())-IIf(DateSerial(Year(Date()),7,1)>Date(),1,0),7,1) And
Date()

Have attempted to change the first DateSerial with no success.

Thanks in advance.

Andrew

 

[John Spencer]

I think the following *MIGHT* work for you.

The last day of the previous month can be calculated as
DateSerial(Year(Date()),Month(Date()),0)

The July 1 previous to the current date can be calculated as follows
(test this one as I did it off the top of my head):
DateSerial(Year(DateAdd("m",-6,Date())),7,1)


Between DateSerial(Year(DateAdd("m",-6,Date())),7,1) And
DateSerial(Year(Date()),Month(Date()),0)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

I am working on a report for which I need the result from the beginning of
the Financial Year (in my case July 01) to the previous month. I have a query
using the following to generate the range - unfortunately it produces a range
including the current month.

Between
DateSerial(Year(Date())-IIf(DateSerial(Year(Date()),7,1)>Date(),1,0),7,1) And
Date()

Does this do what you want?

Between DateSerial(Year(Date()) -
IIf(DateSerial(Year(Date()), 7, 1) > Date(), 1, 0), 7, 1)
And DateSerial(Year(Date()), Month(Date()) - 1, 0)

 

[Andrew Glennie]

Thanks Both,

The answer was yours Marsh apart from needing to remove the -1 after the
last Month(Date). Now works wonderfully. I was modifying the wrong section of
the formula (the first). Doh!

Andrew

 

[Marshall Barton]

Thanks Both,

The answer was yours Marsh apart from needing to remove the -1 after the
last Month(Date). Now works wonderfully. I was modifying the wrong section of
the formula (the first).

Good catch about the extra -1. Using 0 for the day of the
month was sufficient to get to the end of the previous
month.