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.
> >>>
> >> .
> >>
> .
>
|
Similar Threads
