Calculating the days in a month

[Leo]

I have a Control which is a Date field named 'NEWTOPD'. Based on the date
entered in the field it calculates the days offor the whole month making
amends for Leap-year as well. If I want only the number of days in that month
starting from the date entered in 'NEWTOPD' - to be entered in a calculated
text field (which is a number field) named 'Text361' how should I modify the
following code? Text361 stores the data in (PDDAYS).

'UPDATE (PDDAYS) BASED ON NEWTOPD DATE

Dim iMon As Integer
Dim sDate As String

sDate = (NEWTOPD)

If sDate = "" Then Exit Sub

Select Case Month(sDate)
Case 4, 6, 9, 11
[Text361] = "30"
Case 1, 3, 5, 7, 8, 10, 12
[Text361] = "31"
Case 2
If Year(sDate) Mod 4 = 0 Then
[Text361] = "29"
Else: [Text361] = "28"
End If
End Select

Can someone help?
Thanking you in advance,
Sincerely
Leo

 

[Leo]

Nevermind I have solved that problem!
Thanks

 

[John W. Vinson]

I have a Control which is a Date field named 'NEWTOPD'. Based on the date
entered in the field it calculates the days offor the whole month making
amends for Leap-year as well. If I want only the number of days in that month
starting from the date entered in 'NEWTOPD' - to be entered in a calculated
text field (which is a number field) named 'Text361' how should I modify the
following code? Text361 stores the data in (PDDAYS).

Set the control source of Text361 (which you should certainly rename to
something meaningful) to

=DateDiff("d", [NEWTOPD], DateSerial(Year([NEWTOPD]), Month([NEWTOPD]) + 1, 0)

This will return 0 for the 31st of March, 1 for the 30th, etc., and will
handle all months and leap years correctly.

 

[John Spencer]

Simple expression
Day(DateSerial(Year(NewTopD),Month(NewTopD)+1,0))

Explanation the DateSerial expression generates the LAST day of the month by
getting the zero day of next month. The zero-day of a month is one day before
the first day of the month. You could think of this expression as calculating
the first day of the month and then subtracting one from that result.

Day returns the day number of the month.

By the way determining leap year by dividing by 4 works in most cases. There
are exceptions. If the year ends in 00 then the year is a leap year only if
it is divisible by 400. So 1900 is NOT a leap year and 2000 is a leap year.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County