query for financial year

[pedro]

I would like to write a query where I do not have to 'hard code' a date range
for a financial year. The financial year begins 1 July through to 30 June.
The column name I am using is [data awarded]. I assume the the code would be
part of the WHERE clause in the SQL statement.

 

[Allen Browne]

In query design, type an expression like this into the Field row:
FinYearEnd: Year(DateAdd("m", 6, [InvoiceDate]))
substituting your date field for InvoiceDate.

 

[pedro]

Sorry, I should have added that I only want the query to return records from
the current financial year.

Thanks for your help.

In query design, type an expression like this into the Field row:
FinYearEnd: Year(DateAdd("m", 6, [InvoiceDate]))
substituting your date field for InvoiceDate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would like to write a query where I do not have to 'hard code' a date
range
for a financial year. The financial year begins 1 July through to 30 June.
The column name I am using is [data awarded]. I assume the the code would
be
part of the WHERE clause in the SQL statement.

 

[raskew via AccessMonster.com]

Pedro -
Here's a sample query based on Northwind's Orders table. It'll prompt you
for the FY start date, e.g. 7/1/94, and will return all records with an Order
date between 7/1/94 and 6/30/95. You should be able to adapt it to your
table and field names:

SELECT
Orders.OrderID
, Orders.CustomerID
, Orders.OrderDate
, Year([fy start date]) AS PolicyYear
FROM
Orders
WHERE
(((Orders.OrderDate) Between [fy start date]
AND
DateAdd("yyyy",1,[fy start date])-1))
ORDER BY
Orders.OrderDate;

HTH - Bob

I would like to write a query where I do not have to 'hard code' a date range
for a financial year. The financial year begins 1 July through to 30 June.
The column name I am using is [data awarded]. I assume the the code would be
part of the WHERE clause in the SQL statement.

 

[Allen Browne]

Current Financial year starts:
DateSerial(Year(DateAdd("m", -6, Date()),7,1)

You can work out the Between expression for your criteria from that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry, I should have added that I only want the query to return records
from
the current financial year.

Thanks for your help.

In query design, type an expression like this into the Field row:
FinYearEnd: Year(DateAdd("m", 6, [InvoiceDate]))
substituting your date field for InvoiceDate.

 

[pedro]

I get an error message in the between expression:

WHERE [date awarded] between DateSerial(Year(DateAdd("m", -6, Date()),7,1)
and ?

Thanks for your help.

Current Financial year starts:
DateSerial(Year(DateAdd("m", -6, Date()),7,1)

You can work out the Between expression for your criteria from that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry, I should have added that I only want the query to return records
from
the current financial year.

Thanks for your help.

In query design, type an expression like this into the Field row:
FinYearEnd: Year(DateAdd("m", 6, [InvoiceDate]))
substituting your date field for InvoiceDate.

 

[Allen Browne]

Okay, I missed a bracket:
DateSerial(Year(DateAdd("m", -6, Date())),7,1)

I'm sure you can work out out to get Year(Date()), and use DateSerial() with
that for 6th month and 30th day.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I get an error message in the between expression:

WHERE [date awarded] between DateSerial(Year(DateAdd("m", -6, Date()),7,1)
and ?

Thanks for your help.

Current Financial year starts:
DateSerial(Year(DateAdd("m", -6, Date()),7,1)

You can work out the Between expression for your criteria from that.

 

[raskew via AccessMonster.com]

You're welcome. Not a problem.

Pedro -
Here's a sample query based on Northwind's Orders table. It'll prompt you
for the FY start date, e.g. 7/1/94, and will return all records with an Order
date between 7/1/94 and 6/30/95. You should be able to adapt it to your
table and field names:

SELECT
Orders.OrderID
, Orders.CustomerID
, Orders.OrderDate
, Year([fy start date]) AS PolicyYear
FROM
Orders
WHERE
(((Orders.OrderDate) Between [fy start date]
AND
DateAdd("yyyy",1,[fy start date])-1))
ORDER BY
Orders.OrderDate;

HTH - Bob

I would like to write a query where I do not have to 'hard code' a date range
for a financial year. The financial year begins 1 July through to 30 June.
The column name I am using is [data awarded]. I assume the the code would be
part of the WHERE clause in the SQL statement.