New column to get fiscal year from existing dates

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
 
D

David Z

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
 
J

John W. Vinson

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

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.
 
J

Jeff Boyce

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.
 
S

sonofroy

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

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.
 
S

sonofroy

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.




.
 
S

sonofroy

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

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.
 
S

sonofroy

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

and I got 5217

I then tried to reverse them and it works

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

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top