Financial Year not Calendar Year

[Kirsti]

Hi,

I have queries where the criteria on a date field is =Year(Now()) And Month(Now()-1).



However, I've just been told that all reporting needs to be based on the Financial year (July to June) instead of the calendar year.

I'm just wondering how I can achieve this, without hardcoding dates in my queries. Is it possible to write a function in VBA, and then call that function from the query? If so, how would this look?

Or, can I create a table with dates, and call that? (At least then it would only need to be updated in one place).

Any suggestions greatfully received!

Kirsti

[ketbdnetbp]

Kirsti - Personally, I have not used functions in queries. However, I do a great deal of financial reporting. I have found that a simple form with two date textboxes, FROM and TO, will return the records I need if the underlying recordset has a date based field. So, my suggestion would be to create a simple unbound form with two text boxes (format as date), named: dteF1 and dteT1. Then, in the queries where you need to filter by date, put BETWEEN [Forms]![theunboundformsname]![dteF1] AND [Forms]![theunboundformsname]![dteT1] in the criteria row of the date field. This should return all records between those dates (this will handle any period). When finished, add a button or other control to the unbound form that will open the desired object or execute a particular action. That event procedure should also double check dteF1 and dteT1 for valid dates, as a precaution.

If needed, you could always add button(s), that will set the dates of dteF1 and dteT1 for periods like most recent month, year to date, and fiscal year. I have found buttons like these to be particularly useful and helpful for users as well as fairly efficient.

Hope this helps,

Jim

[Kirsti]

Fantastic - thanks Jim, I will give it a try and let you know how I get on.

Kirsti

[Kirsti]

Hi Jim, I followed your instructions, but (I'm not experienced with using unbound text boxes...) I couldn't get the actual dates (dteF1 = 01/07/2011 and dteT1 = 30/06/2012) to save, therefore when I tested it with a query, it became a paramater query, asking me to input the dates...

I tried putting the dates into a table, but had the same result.

[Kirsti]

Ok, I've figured out how to save the actual dates in the form (needed "" around the dates in Default Value), but when I run my query it is still asking me to enter a parameter value.

[Kirsti]

Got it!! Didn't realise the Form had to be OPEN!

Now working perfectly.

Thanks Jim.