ユーザー関数

年齢を調べる

'年齢を返す
Function GetAge(birthday As Date, theDate As Date) As Integer
   If AfterThisYearsBirthday(birthday, theDate) Then
       GetAge = Year(theDate) - Year(birthday)
   Else
       GetAge = Year(theDate) - Year(birthday) - 1
   End If
End Function

' 現在、今年の誕生日が来ているか？
Function AfterThisYearsBirthday(birthday As Date, theDate As Date) As Boolean
   If Month(birthday) < Month(theDate) Or _
       (Month(birthday) = Month(theDate) And Day(birthday) <= Day(theDate)) Then
       AfterThisYearsBirthday = True
   Else
       AfterThisYearsBirthday = False
   End If
End Function
------------------------------------------------------------------------------------
[[クエリ]]→　年齢: GetAge([生年月日],Now())

勤続年数の抽出

Function myDateDiff(D1, D2) As Variant

If (IsDate(D1) And IsDate(D2)) = False Then Exit Function

Dim IntVar As Integer

   '年数
   IntVar = Format(D1, "mmdd") > Format(D2, "mmdd")
   IntVar = DateDiff("yyyy", D1, D2) + IntVar
   myDateDiff = IntVar & "年"
   
   '月数
   IntVar = DateDiff("m", D1, D2) - IntVar * 12
   myDateDiff = myDateDiff & IntVar & "ヶ月"
   
'    '日数
'    IntVar = Day(D1) > Day(D2)
'    IntVar = DateDiff("d", DateSerial(Year(D2), Month(D2) + IntVar, Day(D1)), D2)
'    myDateDiff = myDateDiff & IntVar & "日"
   
End Function
------------------------------------------------------------------------------------
クエリ→　勤続年数: myDateDiff([入社年月日],Now())

ログインユーザー名取得

Private Declare Function WNetGetUser Lib "mpr" Alias "WNetGetUserA" (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long
   
Function UserName() As String
   
Dim strUserName As String * 255
If WNetGetUser("", strUserName, 255) = 0 Then
    UserName = Left$(strUserName, InStr(strUserName, Chr$(&H0)) - 1)
Else
    UserName = ""
End If
   
End Function