Number Of Days Per Month

[djwasim]

Hello

I hv 2 fields named

1- date
2- dayspermonth

i am usitng to enter date in "date" field like 12-Jun-2010
now i want to calculate automatically the number of days in the month of Jun in the field named "dayspermonth"

plz tell me is it possible?

 

[namliam]

what is the days per month in Jun?
30?
12?
18?

Yes any calculation on dates to get any of these values is possible.

 

[missinglinq]

[B]Me.DaysPerMonth = Day(DateSerial(Year(YourDate), Month(YourDate) + 1, 0))[/B]

Note that Date is a Reserved Word in Access/VBA and should not be used as a field/control name! You'll confuse the Access Gnomes no end! Change it to something like YourDate in the code above.

 

[djwasim]

thanx dear
there is another problem i have shared last day by the title of "auto resize the image in a form with screen size"
but i cant get reply of any person
plz tell me solution of that proble.

 

[Brianwarnock]

what is the days per month in Jun?
30?
12?
18?

Yes any calculation on dates to get any of these values is possible.

I am confused by this post, what was its purpose?

Brian

 

[missinglinq]

I believe the Backwards Mailman was attempting to be humorous, not being familiar with the month of Jun!

 

[namliam]

I am confused by this post, what was its purpose?

Brian

Well from 12 june you can figure there is atleast 3 different days of the month to get...
30 days in the month, but then why 12 june and not 1 june or...
18 days left in the month
12 days already in the month

So I was just wondering what the OP was looking for, 30 seems the 'obvious' answer though

 

[Brianwarnock]

Thanks for clearing that up, I was a bit slow to grasp the significance of the 12 and 18. Perhaps just asking him what he wanted would have been simpler.

Brian

 

[cheekybuddha]

What is CB?

 

[CJ_London]

I suspect after 13 years the op has a solution

 

[Gasman]

What is CB?

VB? C key is adjacent to the V key

Search here. @arnelgp has provided a few age functions, one of which does what you require.

 

[cheekybuddha]

VB? C key is adjacent to the V key

Thanks gm, more coffee required I think!

 

[cheekybuddha]

@lamywaby,

Here is a function by Gustav Brock that should do what you want (Found here):

' Formats the output from AgeMonthsDays.
'
' 2020-10-05. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function FormatAgeYearsMonthsDays( _
    ByVal DateOfBirth As Date, _
    Optional ByVal AnotherDate As Variant) _
    As String

    Dim Years       As Integer
    Dim Months      As Integer
    Dim Days        As Integer
    Dim YearsLabel  As String
    Dim MonthsLabel As String
    Dim DaysLabel   As String
    Dim Result      As String
    
    Months = AgeMonthsDays(DateOfBirth, AnotherDate, Days)
    Years = Months \ MonthsPerYear
    Months = Months Mod MonthsPerYear
    
    YearsLabel = "year" & IIf(Years = 1, "", "s")
    MonthsLabel = "month" & IIf(Months = 1, "", "s")
    DaysLabel = "day" & IIf(Days = 1, "", "s")
    
    ' Concatenate the parts of the output.
    Result = CStr(Years) & " " & YearsLabel & ", " & CStr(Months) & " " & MonthsLabel & ", " & CStr(Days) & " " & DaysLabel
    
    FormatAgeYearsMonthsDays = Result
    
End Function

Pass in the start date and optional end date.

The function formats the output of another function found here:

' Returns the difference in full months from DateOfBirth to current date,
' optionally to another date.
' Returns by reference the difference in days.
' Returns zero if AnotherDate is earlier than DateOfBirth.
'
' Calculates correctly for:
'   leap Months
'   dates of 29. February
'   date/time values with embedded time values
'   any date/time value of data type Date
'
' DateAdd() is, when adding a count of months to dates of 31th (29th),
' used for check for month end as it correctly returns the 30th (28th)
' when the resulting month has 30 or less days.
'
' 2015-11-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function AgeMonthsDays( _
    ByVal DateOfBirth As Date, _
    Optional ByVal AnotherDate As Variant, _
    Optional ByRef Days As Integer) _
    As Long
    
    Dim ThisDate    As Date
    Dim Months      As Long
      
    If IsDateExt(AnotherDate) Then
        ThisDate = CDate(AnotherDate)
    Else
        ThisDate = Date
    End If
    
    ' Find difference in calendar Months.
    Months = DateDiff("m", DateOfBirth, ThisDate)
    If Months < 0 Then
        Months = 0
    Else
        If Months > 0 Then
            ' Decrease by 1 if current date is earlier than birthday of current year
            ' using DateDiff to ignore a time portion of DateOfBirth.
            If DateDiff("d", ThisDate, DateAdd("m", Months, DateOfBirth)) > 0 Then
                Months = Months - 1
            End If
        End If
        ' Find difference in days.
        Days = DateDiff("d", DateAdd("m", Months, DateOfBirth), ThisDate)
    End If
        
    AgeMonthsDays = Months
 
End Function