using part of date

[Rebecca]

1) In a field, I have the fiscal year (2009).
I now want to enter a set date, with the year of the date based on the
fiscal year.
7/1/__ -- in which the year is a year less than the fiscal year field (so
for fiscal year 2009, it would be 7/1/08); for FY2010, it would be 7/1/09)

How do I do this?
I can do "fiscal year - 1" to get 2008; but then when I go to a new field
and enter "7/1/[new year]" (or #7/1/[new year]#) it doesn't work.

2) Also, I'm not sure the fiscal year is recognized as a date, but when I
try to convert it (CDate or CVDate), I get "1905" as the result.

Thanks for your help.

 

[KARL DEWEY]

when I try to convert it (CDate or CVDate), I get "1905" as the result.
Access stores dates as a decimal number counting days from 31 December 1899
or so.

Try this if your field is named [fiscal year]- --
DateSerial([fiscal year]-1,7,1)

 

[Rebecca]

Wow, that worked perfectly. And so easy! Thanks Karl.

Access stores dates as a decimal number counting days from 31 December 1899
or so.

Try this if your field is named [fiscal year]- --
DateSerial([fiscal year]-1,7,1)


--
KARL DEWEY
Build a little - Test a little

1) In a field, I have the fiscal year (2009).
I now want to enter a set date, with the year of the date based on the
fiscal year.
7/1/__ -- in which the year is a year less than the fiscal year field (so
for fiscal year 2009, it would be 7/1/08); for FY2010, it would be 7/1/09)

How do I do this?
I can do "fiscal year - 1" to get 2008; but then when I go to a new field
and enter "7/1/[new year]" (or #7/1/[new year]#) it doesn't work.

2) Also, I'm not sure the fiscal year is recognized as a date, but when I
try to convert it (CDate or CVDate), I get "1905" as the result.

Thanks for your help.

 

[KARL DEWEY]

One thought.
Most Fiscal years are not offset by a full year but so many months in one
direction or another. So if your Fiscal is displaced by X months from the
calendar then this really will not work for you.
--
KARL DEWEY
Build a little - Test a little

Wow, that worked perfectly. And so easy! Thanks Karl.

when I try to convert it (CDate or CVDate), I get "1905" as the result.

Access stores dates as a decimal number counting days from 31 December 1899
or so.

Try this if your field is named [fiscal year]- --
DateSerial([fiscal year]-1,7,1)


--
KARL DEWEY
Build a little - Test a little

1) In a field, I have the fiscal year (2009).
I now want to enter a set date, with the year of the date based on the
fiscal year.
7/1/__ -- in which the year is a year less than the fiscal year field (so
for fiscal year 2009, it would be 7/1/08); for FY2010, it would be 7/1/09)

How do I do this?
I can do "fiscal year - 1" to get 2008; but then when I go to a new field
and enter "7/1/[new year]" (or #7/1/[new year]#) it doesn't work.

2) Also, I'm not sure the fiscal year is recognized as a date, but when I
try to convert it (CDate or CVDate), I get "1905" as the result.

Thanks for your help.