在 Microsoft Access (2010) 中以年和月为单位计算年龄 [英] calculating age in years and months in Microsoft Access (2010)

问题描述

我有两个字段(体检日期和出生日期).我计算了年龄((体检日期-出生日期)/365.25).我想做的是在不同的领域以年和月为单位计算年龄.我不确定是否可以使用代码构建器或某种方式来完成.

I have two fields (Date of Physical Exam and Date of Birth). I calculated the age in years((Date of Physical Exam- Date of Birth)/365.25). What I want to do is calculate age in years and months in separate field. I am not sure if it can be done using code builder or some way.

推荐答案

虽然 DateDiff() 函数似乎是计算年龄的合乎逻辑的选择，但不幸的是它没有计算 的数量两个日期之间已经过去的整年或整月.例如，假设一个婴儿于 2014 年 12 月 31 日出生，并在恰好 48 小时后，即 2015 年 1 月 2 日接受检查.也就是说，

While the DateDiff() function seems like the logical choice for calculating ages, it unfortunately does not calculate the number of full years or months that have elapsed between two dates. For example, say that a baby was born on December 31, 2014, and was examined exactly 48 hours later, on January 2, 2015. That is,

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

如果我们简单地使用 DateDiff() 来计算她在考试时的年龄"(以年和月为单位)，我们将得到

If we simply used DateDiff() to calculate her "age" in years and months at the time of the exam we would get

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

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

So, we would report that the baby is 1 year and 1 month old, when really she is just 2 days old.

正确的年龄计算需要比这更复杂.以下 VBA 函数将计算以年和月为单位的年龄"，返回类似2 年零 1 个月"的字符串:

Proper age calculations need to be more sophisticated than that. The following VBA function will calculate the "age" in years and months, returning a string like "2 years and 1 month":

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", Date1, 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

这篇关于在 Microsoft Access (2010) 中以年和月为单位计算年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！