access下半年月份是几月份_数据库 – 在Microsoft Access(2010)中计算年和月的年龄

虽然DateDiff()函数似乎是计算年龄的合理选择,但遗憾的是它并不计算两个日期之间经过的整年或月数.例如,假设一个婴儿出生于2014年12月31日,并在2015年1月2日48小时后进行了检查.也就是说,

DateOfBirth = DateSerial(2014, 12, 31)

DateOfExam = DateSerial(2015, 1, 2)

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

?DateDiff("yyyy", DateOfBirth, DateOfExam)

1

?DateDiff("m", DateOfBirth, 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", 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