Dec 11 2021 05:58 PM
Dec 11 2021 05:58 PM
I'm trying to import some Excel data into Access. I have one column in my Excel spreadsheet that performs a calculation on two date cells.
Excel cell D1 has a date value, for this example, let's use a date of 01-01-1990 (i.e., Jan 1, 1990).
Excel cell D3 has a date value of any date after the date entered in A1, let's use 9-8-2021 (Sept 08, 2021).
Excel cell D5 has a formula:
=DATEDIF($D$1,D3,"y") & "-" & DATEDIF($D$1,D3,"ym") & "-" & DATEDIF($D$1,D3,"md")
When using the above example dates entered in D1 & D3 yields a display in D5 of:
i.e., 31 years, 8 months, 7 days, showing the calculation of time (in terms of years, months days) between the two dates.
Can I import this functionality into Access? If so, how is this done?
Idealy I'd like the Access field to be calculated and displayed as:
31Y, 08M, 07D
but I'll take anything I can get.
Dec 12 2021 07:57 AM
You can create a new, calculated field in an Access table. The expression would be different, of course.
That said, we discourage calculated fields in relational database application tables. They belong in queries or controls on forms or reports, not in tables.
In Excel you have no choice. Everything is in one flat interface--the worksheet.
Access is not subject to that limitation and the interface offers much richer alternatives. Therefore, don't think about storing the calculation as part of the data. Think about DISPLAYING it, only when needed, in a form or report.
Dec 12 2021 08:04 AM - edited Dec 12 2021 09:35 AM
Because you want the years, months and days elapsed, it needs to be done in a VBA function, rather than as a calculated value in a field.
Public Function getAge(ByVal vDateOfBirth As Variant, ByVal vDateReference As Variant) As String
Dim idays As Integer
Dim iMonths As Integer
Dim iYears As Integer
Dim iDaysInMonth As Integer
Dim dDateOfBirth As Date
Dim dDateReference As Date
dDateOfBirth = Int(Nz(vDateOfBirth, Now()))
dDateReference = Int(Nz(vDateReference, Now()))
iDaysInMonth = DateSerial(Year(dDateOfBirth), Month(dDateOfBirth) + 1, 1) - DateSerial(Year(dDateOfBirth), Month(dDateOfBirth), 1)
iYears = DatePart("yyyy", dDateReference) - DatePart("yyyy", dDateOfBirth)
iMonths = DatePart("m", dDateReference) - DatePart("m", dDateOfBirth)
idays = DatePart("d", dDateReference) - DatePart("d", dDateOfBirth)
If idays < 0 Then
iMonths = iMonths - 1
idays = iDaysInMonth + idays
If iMonths < 0 Then
iYears = iYears - 1
iMonths = 12 + iMonths
getAge = iYears & " yr(s). " & iMonths & " mo(s). " & idays & " day(s)"