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
(year value of finYear) = intYear else(year value of finYear) = intYear-1 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?
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)
7 2043
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 32,556
Expert Mod 16PB
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?
@TheSmileyOne
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
In SQL it would be something like : - WHERE [finYear]>=CDate('1 Oct ' & Year(Date())-IIf(Month(Date())>9,0,1))
Sign in to post your reply or Sign up for a free account.
Similar topics
by: middletree |
last post by:
I know that if I have a full timestamp, and wrap the DateValue function
around it, it will give me mm/dd/yyyy. I can't seem to find a similar
function which would return a 2-digit year. Is there...
|
by: Chumley the Walrus |
last post by:
I'm trying to use DatePart and Datediff functions to show the
formatted m/dd/yyyy display for the dates of Monday and Friday of the
current week. I want it to display as:
Our Weekly Schedule...
|
by: Bill |
last post by:
I have a date that I get from my now() function that returns:
2/10/2004 2:17:16 PM
I would like to be able to break this value up. Is there a function that
allows me to pull whatever I want...
|
by: Lin Ma |
last post by:
In my asp page, I have a date value from SQL database and I would like to be
formatted like: yyyy,mm,dd.
Such as 1/22/2005 to be 2005,1,22
Is there a simple command I can use?
Thanks,
|
by: AGB |
last post by:
Hi all,
I need a script that tells the user what the date is for monday and
what the date is for friday for the current week based on the current
day.
Any help?
|
by: haydn_llewellyn |
last post by:
Hi,
My company runs on a fiscal calendar that starts on the first monday in
July, and is based on a 13 week quarter (4 weeks, 4 weeks, 5 weeks).
What I need, is a way of relating Date() to the...
|
by: S. van Beek |
last post by:
Dear reader,
With DatePart() you can subtract the year or the week from a date field
with:
DatePart("yyyy";) for the year
|
by: Russell Mangel |
last post by:
I have written the following program using VS2005. The program is a Dynamic
Array similar to System.Collections.ArrayList in .NET. The program works
okay until I reach 65536, I can't seem to figure...
|
by: Martin |
last post by:
I'm retrieving some records from a database. One of the fields
contains a date/time. I would like to format it as I send it out to
the table in the displayed page.
Can some one please tell me...
|
by: jmar93 |
last post by:
HI,
I am using Access 2007 and have a query that returns values for the next week using the following code:
Year()*53+DatePart("ww",)=Year(Date())*53+DatePart("ww",Date())+1.
It has worked...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |