Selecting date fields

[Guest]

Our fiscal year runs from April 1 to March 31, so it spans two calendar years.

My database records expenses along with the date (i.e., a field for expense
and a field for date).

I want to be able to sum all the expenses during our current fiscal year.
Summing is the easy part. What's the formula for selecting dates back to the
most recent April 1 (which might be this calendar year, or last year)?

Thanks in advance.

Jerry

 

[Guest]

Try this --
DatePart("yyyy",DateAdd("m",-3,[YourDateField]))

Then for criteria use a prompt like --
[Enter FY - 2003]

 

[Guest]

Anyone know of a way to do it that doesn't prompt you for an entry each time
the report is run?

Jerry

Try this --
DatePart("yyyy",DateAdd("m",-3,[YourDateField]))

Then for criteria use a prompt like --
[Enter FY - 2003]

OUr fiscal year runs May - April. I need to create a report that spans

Our fiscal year runs from April 1 to March 31, so it spans two calendar years.

My database records expenses along with the date (i.e., a field for expense
and a field for date).

I want to be able to sum all the expenses during our current fiscal year.
Summing is the easy part. What's the formula for selecting dates back to the
most recent April 1 (which might be this calendar year, or last year)?

Thanks in advance.

Jerry

 

[Guest]

Between
DateAdd("yyyy",-1,DateSerial(DatePart("yyyy",DateAdd("m",-3,Date())),3,31))+1
And DateSerial(DatePart("yyyy",DateAdd("m",-3,Date())),3,31)

Anyone know of a way to do it that doesn't prompt you for an entry each time
the report is run?

Jerry

Try this --
DatePart("yyyy",DateAdd("m",-3,[YourDateField]))

Then for criteria use a prompt like --
[Enter FY - 2003]

OUr fiscal year runs May - April. I need to create a report that spans

Our fiscal year runs from April 1 to March 31, so it spans two calendar years.

My database records expenses along with the date (i.e., a field for expense
and a field for date).

I want to be able to sum all the expenses during our current fiscal year.
Summing is the easy part. What's the formula for selecting dates back to the
most recent April 1 (which might be this calendar year, or last year)?

Thanks in advance.

Jerry

 

[Guest]

Earlier post for prior FY. This for current FY
Between DateSerial(DatePart("yyyy",DateAdd("m",-3,Date())),4,1) And
DateSerial(DatePart("yyyy",DateAdd("m",+9,Date())),3,31)

Anyone know of a way to do it that doesn't prompt you for an entry each time
the report is run?

Jerry

Try this --
DatePart("yyyy",DateAdd("m",-3,[YourDateField]))

Then for criteria use a prompt like --
[Enter FY - 2003]

OUr fiscal year runs May - April. I need to create a report that spans

Our fiscal year runs from April 1 to March 31, so it spans two calendar years.

My database records expenses along with the date (i.e., a field for expense
and a field for date).

I want to be able to sum all the expenses during our current fiscal year.
Summing is the easy part. What's the formula for selecting dates back to the
most recent April 1 (which might be this calendar year, or last year)?

Thanks in advance.

Jerry