Prev: how to run onhand value report
Next: mdb file
From: Leo on 6 Mar 2010 21:50
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
From: Leo on 6 Mar 2010 23:05
Nevermind I have solved that problem!
Thanks

"Leo" wrote:

> 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
From: John W. Vinson on 7 Mar 2010 01:07
On Sat, 6 Mar 2010 18:50:01 -0800, Leo <Leo(a)discussions.microsoft.com> wrote:

>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 W. Vinson [MVP]
From: John Spencer on 7 Mar 2010 08:12
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

Leo wrote:
> 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
 | 
Pages: 1
Prev: how to run onhand value report
Next: mdb file