How to program date with dynamic datepart

[colintis]

I want to write a code that can automatically change the year of a date value after getting into a new financial year (e.g. October). Here's only the concept of what I had in mind

Dim finYear as Date
Dim intYear as Integer

finYear = DateValue("01/10/2000")
intYear = Year(Date)

'Below will be in words instead of code
if (Month(date) >= Month(finYear)) then[INDENT](year value of finYear) = intYear[/INDENT]
else[INDENT](year value of finYear) = intYear-1[/INDENT]
end if

For example, when I get into Oct, 2009, the finYear will update its year value to 2009 (from y2k). If I'm running the codes again in e.g. Mar, 2010, the finYear will update its year value to 2009 as well.

I want to directly change the year value of finYear. Is there other ways instead of using a loop to do DateAdd until it reach the current year?

[TheSmileyCoder]

I've read your post 3 times, and I sorry, but I still don't understand what is is your trying to do.

Could you try explaining in another way, and also explain why you need this function, as a bit of context usually makes understanding alot easier.

[NeoPa]

So, you're looking to return the date of the most recent occurrence of the day in the year of the date passed? Currently, a date of 1st March 2000 should return a value of 1st March 2010 - Yes?

Is a function what you really need? Or do you need data stored somewhere to be updated too?

[colintis]

I've read your post 3 times, and I sorry, but I still don't understand what is is your trying to do.

Could you try explaining in another way, and also explain why you need this function, as a bit of context usually makes understanding alot easier.

Sorry for my poor method of presenting what I tried to say.

All I want to know is to see if there's a function that can modify the year of a date variable. From the code above, I want to change it directly from 1st March 2000 to 1st March 2010, without doing any calculation on their difference then using dateadd function.

[TheSmileyCoder]

I don't know of any such function, and I can imagine it might give errors, say the date was the 29th of february 2004, and you suddenly make it 29th of february 2009 which has no february 29th.

Is there any reason why you cannot use the DataAdd function? Something like this:

Dim intYear as integer
  intYear=Year(dtInput)
Dim intWantedYear as Integer
  intWantedYear=Year(Date())
  dtOutput=DateAdd(dtInput,"yyyy",intWantedYear-intYear)

[NeoPa]

Specific questions are often asked as members tend to find explaining problems unambiguously quite difficult. Your term "date variable" in your latest explanation could have various interpretations for instance. If you could see you way to giving a direct answer to the question I could better advise on an appropriate way forward.

NB. Smiley, the year value in DateAdd() is "yyyy" rather than "y". This approach can work of course, but as you've found requires some preparation beforehand. If this is exclusively SQL then this would force use of a VBA function in the code.

[colintis]

Smiley, the error you mentioned would not be possible as the date value I have will be only fixed to the 1st Oct., so its ignorable...

The case story about this question I have, is I need to generate the report to excel, with records from different tables that are limited to current financial year of a company. While such as now is May, 2010. I need to get records from 1st Oct, 2009 to May 2010. So in date value, only day and month are fixed while year is dynamic whenever it jumps to the next financial year.

I know I can do that in SQL, but as it takes more lines of coding and there's error with putting the WHERE clause into the query I had created....

Also Smiley's example code gave me an idea to turn the way around which might be a success. Thanks for enlighting me :)

[NeoPa]

In SQL it would be something like :

WHERE [finYear]>=CDate('1 Oct ' & Year(Date())-IIf(Month(Date())>9,0,1))