New column to get fiscal year from existing dates

  • Thread starter Thread starter sonofroy
  • Start date Start date
S

sonofroy

I have ready all the posts and they have not helped so I am asking for this
one. I have a table with a date field "mm/dd/yyyy" in it. I am trying to
create a query that creates another field that only shows what fiscal year
"yyyy" that would be. Our fiscal calendar is July -June. How would I do that?
Thanks
 
You can use an IIF in a query like this:

FYCheck: IIf([TestDate]>CVDate("6/30/" &
Year([testdate])),Year([testdate])+1,Year([testdate]))

Where TestDate is your date field. The Year function pulls the year value of
the date. The CVDate function converts text string to a date value. By using
the year or year + 1, this will work for any list of dates.

David
 
I have ready all the posts and they have not helped so I am asking for this
one. I have a table with a date field "mm/dd/yyyy" in it. I am trying to
create a query that creates another field that only shows what fiscal year
"yyyy" that would be. Our fiscal calendar is July -June. How would I do that?
Thanks
Click to expand...

If FY 2010 started on July 1, 2009, then

Year(DateAdd("m", [datefield], 6))

should work.

It will take (say) November 13, 2009, add six months to get April 13, 2010,
and extract the year 2010; April 13, 2009 will increment to November 13, 2009
and return 2009.
 
Everyone's Fiscal Year starts on a different date.

The state I work for has a fiscal year starting on 7/1 ... so we're already
in FY2010.

The federal government (US) starts in October, I think ...

When does YOUR fiscal year start? And do you call it the Fiscal Year for
the year it ENDS, or the year it STARTS?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
This was perfect. Thank you very much!

John W. Vinson said:
I have ready all the posts and they have not helped so I am asking for this
one. I have a table with a date field "mm/dd/yyyy" in it. I am trying to
create a query that creates another field that only shows what fiscal year
"yyyy" that would be. Our fiscal calendar is July -June. How would I do that?
Thanks
Click to expand...

If FY 2010 started on July 1, 2009, then

Year(DateAdd("m", [datefield], 6))

should work.

It will take (say) November 13, 2009, add six months to get April 13, 2010,
and extract the year 2010; April 13, 2009 will increment to November 13, 2009
and return 2009.
Click to expand...
 
We are exactly like you

Jeff Boyce said:
Everyone's Fiscal Year starts on a different date.

The state I work for has a fiscal year starting on 7/1 ... so we're already
in FY2010.

The federal government (US) starts in October, I think ...

When does YOUR fiscal year start? And do you call it the Fiscal Year for
the year it ENDS, or the year it STARTS?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.




.
Click to expand...
 
now I am getting a return of "5217" when it looks at "1/1/2009" Any idea why?

sonofroy said:
This was perfect. Thank you very much!

John W. Vinson said:
I have ready all the posts and they have not helped so I am asking for this
one. I have a table with a date field "mm/dd/yyyy" in it. I am trying to
create a query that creates another field that only shows what fiscal year
"yyyy" that would be. Our fiscal calendar is July -June. How would I do that?
Thanks
Click to expand...

If FY 2010 started on July 1, 2009, then

Year(DateAdd("m", [datefield], 6))

should work.

It will take (say) November 13, 2009, add six months to get April 13, 2010,
and extract the year 2010; April 13, 2009 will increment to November 13, 2009
and return 2009.
Click to expand...
Click to expand...
 
Year(DateAdd("m", [datefield], 6))

and I got 5217

I then tried to reverse them and it works

Year(DateAdd("m", 6, [datefield]))

Thanks
 
Back
Top