G
Guest
Is there a built-in function in VBA Access that returns the number of days in
a month in any given year? Or you have to develop your own.
Thanks in advance
a month in any given year? Or you have to develop your own.
Thanks in advance
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Allen Browne
Try:
Day(DateSerial([WhatYear], [WhatMonth] + 1, 0))
Day zero of the next month is the last day of the month.
Access figures these things out correctly even for day 32 and month 13.
Day(DateSerial([WhatYear], [WhatMonth] + 1, 0))
Day zero of the next month is the last day of the month.
Access figures these things out correctly even for day 32 and month 13.
D
David C. Holley
You may want to clarify you question. With the exception of Februrary,
the number of days in a month stays the same.
the number of days in a month stays the same.
G
Guest
Thanks, Allen, I learned at least one new thing already today. I find
playing with date logic fun and this post will add to my bag of tricks. I
have been using a much more complex method to do this.
playing with date logic fun and this post will add to my bag of tricks. I
have been using a much more complex method to do this.
Allen Browne said:Try:
Day(DateSerial([WhatYear], [WhatMonth] + 1, 0))
Day zero of the next month is the last day of the month.
Access figures these things out correctly even for day 32 and month 13.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
V Ramos said:Is there a built-in function in VBA Access that returns the number of days
in
a month in any given year? Or you have to develop your own.
Thanks in advance
D
David C. Holley
So if you enjoy playing with data logic, are you up for a discussion on
temporal causality & mechanics?
temporal causality & mechanics?
Thanks, Allen, I learned at least one new thing already today. I find
playing with date logic fun and this post will add to my bag of tricks. I
have been using a much more complex method to do this.
:
Try:
Day(DateSerial([WhatYear], [WhatMonth] + 1, 0))
Day zero of the next month is the last day of the month.
Access figures these things out correctly even for day 32 and month 13.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Is there a built-in function in VBA Access that returns the number of days
in
a month in any given year? Or you have to develop your own.
Thanks in advance
G
Guest
LOL, I said datE not datA. Som temporal causality & mechanics? Is that like
getting hit it the side of the head with a wrench?
getting hit it the side of the head with a wrench?
David C. Holley said:So if you enjoy playing with data logic, are you up for a discussion on
temporal causality & mechanics?
Thanks, Allen, I learned at least one new thing already today. I find
playing with date logic fun and this post will add to my bag of tricks. I
have been using a much more complex method to do this.
:
Try:
Day(DateSerial([WhatYear], [WhatMonth] + 1, 0))
Day zero of the next month is the last day of the month.
Access figures these things out correctly even for day 32 and month 13.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Is there a built-in function in VBA Access that returns the number of days
in
a month in any given year? Or you have to develop your own.
Thanks in advance
G
Guest
David C. Holley said:You may want to clarify you question. With the exception of Februrary,
the number of days in a month stays the same.
Your right...that's really my problem...handling the leap year issue...right
now im tempted to build a class just to handle this...so i was thinking if
there is a shorter way to get this information from any built-in classes just
by passing the name of the month and year...well anyway...i'll try Allen's
suggestion first if this will work out...btw...thanks Allen.
ciao.
A
Allen Browne
Function IsLeapYear(iYear As Integer)
IsLeapYear = (Day(DateSerial(iYear, 2, 29) = 29)
End Function
IsLeapYear = (Day(DateSerial(iYear, 2, 29) = 29)
End Function
D
David C. Holley
Here are the rules for determining if a year is a leap year...
http://www.timeanddate.com/date/leapyear.html
1. Every year divisible by 4 is a leap year.
2. But every year divisible by 100 is NOT a leap year
3. Unless the year is also divisible by 400, then it is still a leap
year.
Someone had a similar question awhile back.
The solution is basically a series of If...Thens based on the rules above.
function isLeapYear(year)
isLeapYear = false
if somefunction(year/4) = 0 then isLeapYear = true
if somefunction(year/100) = 0 then isLeapYear = false
if somefunction(year/100) = 0 AND somefunction(year/400) then
isLeapYear = true
end function
someFunction() is the function that I can never remember which returns
the decimal portion of a value. Still a bit asleep. But basically that
will do it. I personally don't the somewhat simpler method of checking
for 29 years in February since I've got a Business Analyst background
and prefer to have my functions based on defined requirements. Since
isLeapYear() is based on the rules, it will always work whereas the
others are assuming that the underlying codeing behind VBA is correct.
http://www.timeanddate.com/date/leapyear.html
1. Every year divisible by 4 is a leap year.
2. But every year divisible by 100 is NOT a leap year
3. Unless the year is also divisible by 400, then it is still a leap
year.
Someone had a similar question awhile back.
The solution is basically a series of If...Thens based on the rules above.
function isLeapYear(year)
isLeapYear = false
if somefunction(year/4) = 0 then isLeapYear = true
if somefunction(year/100) = 0 then isLeapYear = false
if somefunction(year/100) = 0 AND somefunction(year/400) then
isLeapYear = true
end function
someFunction() is the function that I can never remember which returns
the decimal portion of a value. Still a bit asleep. But basically that
will do it. I personally don't the somewhat simpler method of checking
for 29 years in February since I've got a Business Analyst background
and prefer to have my functions based on defined requirements. Since
isLeapYear() is based on the rules, it will always work whereas the
others are assuming that the underlying codeing behind VBA is correct.
D
Douglas J. Steele
See Allen's solution posted elsewhere in this thread for a far simpler
approach, David!
BTW, the "somefunction" you're looking for is Mod, so it would be:
Function isLeapYear(WhatYear)
If WhatYear Mod 4 <> 0 Then
' It's not divisible by 4
isLeapYear = False
Else
If WhatYear Mod 100 <> 0 Then
' It's divisible by 4, but not by 100
isLeapYear = True
Else
' It's divisible by 4 and by 100
' It's only a leap year if it's also
' divisible by 400
isLeapYear = (WhatYear Mod 400 = 0)
End If
End If
End Function
approach, David!
BTW, the "somefunction" you're looking for is Mod, so it would be:
Function isLeapYear(WhatYear)
If WhatYear Mod 4 <> 0 Then
' It's not divisible by 4
isLeapYear = False
Else
If WhatYear Mod 100 <> 0 Then
' It's divisible by 4, but not by 100
isLeapYear = True
Else
' It's divisible by 4 and by 100
' It's only a leap year if it's also
' divisible by 400
isLeapYear = (WhatYear Mod 400 = 0)
End If
End If
End Function
D
David C. Holley
Hey! Its the Business Analyst in me that demands well-defined requirements.
T
Tim Ferguson
BTW, the "somefunction" you're looking for is Mod, so it would be:
fLeapYear = (y mod 4)=0 and ((y mod 100)>0 or (y mod 400)=0)
Hope that helps
Tim F
D
Douglas J. Steele
Tim Ferguson said:fLeapYear = (y mod 4)=0 and ((y mod 100)>0 or (y mod 400)=0)
Very nice, Tim!
D
David C. Holley
That I like.
Very nice, Tim!
D
Douglas J. Steele
I don't understand your comment. As far as I'm concerned
IsLeapYear = (Day(DateSerial(iYear, 2, 29) = 29)
is well-defined: it looks to see whether the 28th day after February 1st in
the year is February 29th or not. If it is, it's a leap year. If it isn't,
it's not.
You could just as easily have written that as
IsLeapYear = (Day(DateAdd("d", 28, DateSerial(iYear, 2, 1)) = 29)
or
IsLeapYear = (Day(DateAdd("d", 1, DateSerial(iYear, 2, 28)) = 29)
if that's clearer.
IsLeapYear = (Day(DateSerial(iYear, 2, 29) = 29)
is well-defined: it looks to see whether the 28th day after February 1st in
the year is February 29th or not. If it is, it's a leap year. If it isn't,
it's not.
You could just as easily have written that as
IsLeapYear = (Day(DateAdd("d", 28, DateSerial(iYear, 2, 1)) = 29)
or
IsLeapYear = (Day(DateAdd("d", 1, DateSerial(iYear, 2, 28)) = 29)
if that's clearer.
D
David C. Holley
You're assuming that DateSerial() and Day() will always function
properly and that both are bug free. Additionally, while it indicates
that the year is a leap year, the logic is not based on the rules for
determining wether or not the year is a leap year or not. Yes if the day
after the 28th day of February is the 29th day of February, the year is
a leap year. However, when the calendar is put together the
determination is based on other things. Having a Business Analyst
background, I am inclined toward an approach that builds code on hard
and fast requirements. Since we know the rules in the real world that
determine a leap year, building code on those rules will result in
stronger code. Asking if the day after the 2/28 is 2/29 is an indicator
of wether or not the year is a leap, it does not DEFINE that the year is
a leap year - which is what we're essentially discussing here. The
difference between DETECTING wether or not a year is a leap year or the
DEFINITION of a leap year.
properly and that both are bug free. Additionally, while it indicates
that the year is a leap year, the logic is not based on the rules for
determining wether or not the year is a leap year or not. Yes if the day
after the 28th day of February is the 29th day of February, the year is
a leap year. However, when the calendar is put together the
determination is based on other things. Having a Business Analyst
background, I am inclined toward an approach that builds code on hard
and fast requirements. Since we know the rules in the real world that
determine a leap year, building code on those rules will result in
stronger code. Asking if the day after the 2/28 is 2/29 is an indicator
of wether or not the year is a leap, it does not DEFINE that the year is
a leap year - which is what we're essentially discussing here. The
difference between DETECTING wether or not a year is a leap year or the
DEFINITION of a leap year.
P
Paul Overway
If you're going to operate on the assumption that DateSerial and Day will
not function as designed, you should seek an alternative language.
Otherwise, you're just goldplating....the rules that you seek to define have
already been defined in the VBA DateSerial function.
not function as designed, you should seek an alternative language.
Otherwise, you're just goldplating....the rules that you seek to define have
already been defined in the VBA DateSerial function.
D
David C. Holley
It a matter of philosophical(sp) approach. I do not know that the rules
have been defined by the VBA DateSerial function as I cannot examine the
code.
have been defined by the VBA DateSerial function as I cannot examine the
code.
P
Paul Overway
Your argument works about as well as saying that because you don't know the
rules defined in the Time, Date, WeekDay, Format, or any other VBA function,
you must write your own. Where are you going to stop? Are you going to
write a new language? Leap years rules have already been defined,
implemented, and tested in DateSerial.
rules defined in the Time, Date, WeekDay, Format, or any other VBA function,
you must write your own. Where are you going to stop? Are you going to
write a new language? Leap years rules have already been defined,
implemented, and tested in DateSerial.
D
David C. Holley
The point is that the purpose of DateSerial() is to build a date. Is was
not designd to determine wether or not a year is a leap year. YES it can
be used to determine if a year is a leap year, however my arguement is
that when you're desiging code it is always best to design it from known
requirements. I am suggesting that a isLeapYear() function be based on
the definition of a leap year as opposed to wether or not the February
of that year has a 29th day. The presence of the 29th day is an
INDICATOR of a leap year, but does not DEFINE a year as a leap year. Its
like determining that a person is a man based on external genetalia as
opposed to chromosomal make-up. You would think that the external
genetalia can be used to determine sex, however that is not the always
the case. The DNA, however is a whole nother story.
not designd to determine wether or not a year is a leap year. YES it can
be used to determine if a year is a leap year, however my arguement is
that when you're desiging code it is always best to design it from known
requirements. I am suggesting that a isLeapYear() function be based on
the definition of a leap year as opposed to wether or not the February
of that year has a 29th day. The presence of the 29th day is an
INDICATOR of a leap year, but does not DEFINE a year as a leap year. Its
like determining that a person is a man based on external genetalia as
opposed to chromosomal make-up. You would think that the external
genetalia can be used to determine sex, however that is not the always
the case. The DNA, however is a whole nother story.
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.