数据库 – 在Microsoft Access(2010)中计算年和月的年龄

我有两个领域(体检日期和出生日期).我计算了年龄((体检日期 – 出生日期)/365.25).我想要做的是在单独的领域中计算年和月的年龄.我不确定是否可以使用代码生成器或某种方式完成.

解决方法

DateOfBirth = DateSerial(2014,12,31)
DateOfExam = DateSerial(2015,1,2)

如果我们只是使用DateDiff()来计算考试时的年和月的“年龄”,我们就会得到

?DateDiff("yyyy",DateOfBirth,DateOfExam)
 1 
?DateDiff("m",DateOfExam)
 1

因此,我们会报告婴儿是1岁零1个月,而实际上她只有2天大.

适当的年龄计算需要比这更复杂.以下VBA函数将计算年和月的“年龄”,返回“2年1个月”之类的字符串：

Public Function AgeInYearsAndMonths(StartDate As Variant,EndDate As Variant) As Variant
    Dim Date1 As Date,Date2 As Date
    Dim mm1 As Integer,dd1 As Integer,mm2 As Integer,dd2 As Integer
    Dim ageYears As Integer,ageMonths As Integer,rtn As Variant
    rtn = Null
    If Not (IsNull(StartDate) Or IsNull(EndDate)) Then
        If StartDate <= EndDate Then
            Date1 = StartDate
            Date2 = EndDate
        Else
            Date1 = EndDate
            Date2 = StartDate
        End If
        mm1 = Month(Date1)
        dd1 = Day(Date1)
        mm2 = Month(Date2)
        dd2 = Day(Date2)
        ageYears = DateDiff("yyyy",Date1,Date2)
        If (mm1 > mm2) Or (mm1 = mm2 And dd1 > dd2) Then
            ageYears = ageYears - 1
        End If
        ageMonths = DateDiff("m",Date2) Mod 12
        If dd1 > dd2 Then
            If ageMonths = 0 Then
                ageMonths = 12
            End If
            ageMonths = ageMonths - 1
        End If
        If ageYears = 0 And ageMonths = 0 Then
            rtn = "less than 1 month"
        Else
            rtn = ageYears & " year" & IIf(ageYears = 1,"","s") & " and " & ageMonths & " month" & IIf(ageMonths = 1,"s")
        End If
    End If
    AgeInYearsAndMonths = rtn
End Function

（编辑：重庆站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!