473,320 Members | 1,612 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to program date with dynamic datepart

255 100+
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

Expand|Select|Wrap|Line Numbers
  1. Dim finYear as Date
  2. Dim intYear as Integer
  3.  
  4. finYear = DateValue("01/10/2000")
  5. intYear = Year(Date)
  6.  
  7. 'Below will be in words instead of code
  8. if (Month(date) >= Month(finYear)) then
    (year value of finYear) = intYear
    else
    (year value of finYear) = intYear-1
    end if
  9.  
  10.  
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?
May 18 '10 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. Dim intYear as integer
  2.   intYear=Year(dtInput)
  3. Dim intWantedYear as Integer
  4.   intWantedYear=Year(Date())
  5.   dtOutput=DateAdd(dtInput,"yyyy",intWantedYear-intYear)

7 2043
TheSmileyCoder
2,322 Expert Mod 2GB
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.
May 18 '10 #2
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?
May 18 '10 #3
colintis
255 100+
@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.
May 18 '10 #4
TheSmileyCoder
2,322 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim intYear as integer
  2.   intYear=Year(dtInput)
  3. Dim intWantedYear as Integer
  4.   intWantedYear=Year(Date())
  5.   dtOutput=DateAdd(dtInput,"yyyy",intWantedYear-intYear)
May 18 '10 #5
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.
May 19 '10 #6
colintis
255 100+
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 :)
May 19 '10 #7
NeoPa
32,556 Expert Mod 16PB
In SQL it would be something like :
Expand|Select|Wrap|Line Numbers
  1. WHERE [finYear]>=CDate('1 Oct ' & Year(Date())-IIf(Month(Date())>9,0,1))
May 19 '10 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

7
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...
3
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...
2
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...
3
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,
2
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?
3
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...
3
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
10
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...
9
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...
11
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...
0
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...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.