If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating Age in Access
Hi
In my database I have 2 dates in my form: Current Date and Date of Emplaoyment. I would like to calculate how many years my employees have been working for me so that I can send the appropriate congratulatory messages. How do I do this? I looked at DateDiff but couldn't figure out how to change it for my purposes. Thank you in advance for your help. |
#3
|
|||
|
|||
Calculating Age in Access
Public Function fAge(dtmDOB, Optional dtmDate)
'Returns the Age in years, for dtmDOB. 'Age calculated as of dtmDate, or as of today if dtmDate is missing. If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then 'set to today's date If IsDate(dtmDOB) Then 'If date passed, then calculate age fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _ (DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) dtmDate) Else fAge = Null End If End Function Or you can use one of the following expressions 'Fails if DOB is null CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) 'Returns Null if DOB is Null CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") Format(Date(),"mmdd")) DateDiff("yyyy",DOB,Date()) + Format(DOB,"mmdd") Format(Date(),"mmdd") John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County forest8 wrote: Hi In my database I have 2 dates in my form: Current Date and Date of Emplaoyment. I would like to calculate how many years my employees have been working for me so that I can send the appropriate congratulatory messages. How do I do this? I looked at DateDiff but couldn't figure out how to change it for my purposes. Thank you in advance for your help. |
#4
|
|||
|
|||
Calculating Age in Access
Do you want to identify when an employee's 'anniversary' is coming up so that
a message can be sent? If so you could use a function like this: Public Function Anniversary(dtmHired As Date, intWeekStarting As Integer) Dim dtmWeekStart As Date, dtmWeekEnd As Date, dtmAnniversaryDate As Date Dim n As Integer dtmWeekStart = VBA.Date - Weekday(VBA.Date, intWeekStarting) + 1 dtmWeekEnd = DateAdd("d", 6, dtmWeekStart) For n = 1 To 50 dtmAnniversaryDate = DateAdd("yyyy", n, dtmHired) If dtmAnniversaryDate = dtmWeekStart Then If dtmAnniversaryDate = dtmWeekEnd Then Anniversary = "Anniversary " & n & " this week on " & dtmAnniversaryDate Else Anniversary = "Next anniversary (" & n & ") on " & dtmAnniversaryDate End If Exit For End If Next n End Function This will identify if an employee's 'anniversary' is in the current week and return a string like: 'Anniversary 6 this week on 02/23/2010' or if not in the current week a string like: 'Next anniversary (6) on 03/23/2010' You can call it in a query, passing the employee's hire date and the first day of the week from which the current week is to be calculated, e.g. if its from Monday, like so: SELECT [EmployeeID], [FirstName], [LastName], Anniversary([Date of Employment],2) AS Message FROM [Employees] SORT BY MONTH([Date of Employment]), DAY([Date of Employment]); Ken Sheridan Stafford, England forest8 wrote: Hi In my database I have 2 dates in my form: Current Date and Date of Emplaoyment. I would like to calculate how many years my employees have been working for me so that I can send the appropriate congratulatory messages. How do I do this? I looked at DateDiff but couldn't figure out how to change it for my purposes. Thank you in advance for your help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#5
|
|||
|
|||
Calculating Age in Access
Hi there
I'm a bit confused about where I should put one of the expressions exactly in my database. I was hoping to use this expression: 'Fails if DOB is null CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) But as I said, where do I put it? Also, does it matter if the date format is mm/dd/yyyy? Thank you "John Spencer" wrote: Public Function fAge(dtmDOB, Optional dtmDate) 'Returns the Age in years, for dtmDOB. 'Age calculated as of dtmDate, or as of today if dtmDate is missing. If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then 'set to today's date If IsDate(dtmDOB) Then 'If date passed, then calculate age fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _ (DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) dtmDate) Else fAge = Null End If End Function Or you can use one of the following expressions 'Fails if DOB is null CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) 'Returns Null if DOB is Null CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") Format(Date(),"mmdd")) DateDiff("yyyy",DOB,Date()) + Format(DOB,"mmdd") Format(Date(),"mmdd") John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County forest8 wrote: Hi In my database I have 2 dates in my form: Current Date and Date of Emplaoyment. I would like to calculate how many years my employees have been working for me so that I can send the appropriate congratulatory messages. How do I do this? I looked at DateDiff but couldn't figure out how to change it for my purposes. Thank you in advance for your help. . |
#6
|
|||
|
|||
Calculating Age in Access
You use the expression as a calculated field in a query
Field: CurrentAge: Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) As the control source of a control in a form or report = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) The format of a date does not matter as long as you are using a datetime type of data. If you are using a string then you do need to convert the string into a datetime type. If you are trying to identify the employees that have an anniversary during a specific time frame (for example, all employees with an employment anniversary this month or this week or tomorrow) then that is a different question. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County forest8 wrote: Hi there I'm a bit confused about where I should put one of the expressions exactly in my database. I was hoping to use this expression: 'Fails if DOB is null CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) But as I said, where do I put it? Also, does it matter if the date format is mm/dd/yyyy? Thank you "John Spencer" wrote: Public Function fAge(dtmDOB, Optional dtmDate) 'Returns the Age in years, for dtmDOB. 'Age calculated as of dtmDate, or as of today if dtmDate is missing. If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then 'set to today's date If IsDate(dtmDOB) Then 'If date passed, then calculate age fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _ (DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) dtmDate) Else fAge = Null End If End Function Or you can use one of the following expressions 'Fails if DOB is null CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) 'Returns Null if DOB is Null CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") Format(Date(),"mmdd")) DateDiff("yyyy",DOB,Date()) + Format(DOB,"mmdd") Format(Date(),"mmdd") John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County forest8 wrote: Hi In my database I have 2 dates in my form: Current Date and Date of Emplaoyment. I would like to calculate how many years my employees have been working for me so that I can send the appropriate congratulatory messages. How do I do this? I looked at DateDiff but couldn't figure out how to change it for my purposes. Thank you in advance for your help. . |
#7
|
|||
|
|||
Calculating Age in Access
Thank you. Until now, I have been able to use little queries and programming
but have progressed to a point where I might need to investigate adding more programming. Thank you again. "John Spencer" wrote: You use the expression as a calculated field in a query Field: CurrentAge: Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) As the control source of a control in a form or report = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) The format of a date does not matter as long as you are using a datetime type of data. If you are using a string then you do need to convert the string into a datetime type. If you are trying to identify the employees that have an anniversary during a specific time frame (for example, all employees with an employment anniversary this month or this week or tomorrow) then that is a different question. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County forest8 wrote: Hi there I'm a bit confused about where I should put one of the expressions exactly in my database. I was hoping to use this expression: 'Fails if DOB is null CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) But as I said, where do I put it? Also, does it matter if the date format is mm/dd/yyyy? Thank you "John Spencer" wrote: Public Function fAge(dtmDOB, Optional dtmDate) 'Returns the Age in years, for dtmDOB. 'Age calculated as of dtmDate, or as of today if dtmDate is missing. If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then 'set to today's date If IsDate(dtmDOB) Then 'If date passed, then calculate age fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _ (DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) dtmDate) Else fAge = Null End If End Function Or you can use one of the following expressions 'Fails if DOB is null CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) 'Returns Null if DOB is Null CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") Format(Date(),"mmdd")) DateDiff("yyyy",DOB,Date()) + Format(DOB,"mmdd") Format(Date(),"mmdd") John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County forest8 wrote: Hi In my database I have 2 dates in my form: Current Date and Date of Emplaoyment. I would like to calculate how many years my employees have been working for me so that I can send the appropriate congratulatory messages. How do I do this? I looked at DateDiff but couldn't figure out how to change it for my purposes. Thank you in advance for your help. . . |
Thread Tools | |
Display Modes | |
|
|