Question Beginners question (1 Viewer)

[rossjackson01]

More questions. Sorry it is so basic. I've been learning for two weeks now.

I have created a table of employees which included DOB. Can I show the age of an employee on a report? if so how? Do I need a field that shows current age and should it be visible to people who might input?

Thank you

Ross

 

[namliam]

No age you dont input because it changes every year....

A quick google will teach you a lot
https://www.google.nl/search?hl=en-.....1ac.1.34.heirloom-hp..0.23.1154.hSvfX62UpEQ

One of the quickest way is to calcaulte it on the fly in a query or report using something like
Year(Date()) - Year(DOB) + (format(DOB, "MMDD") < format(Date(), "MMDD"))

 

[Christos99]

Hi,
No need for a seperate field for age. As it may change, it should be calculated whenever required for display. Try searching the web for 'vba Calculate a persons age given the DOB'
Plenty on there. You can use a custom VBA function from the report field to calc this.

Chris

 

[smig]

Puting an age field is wrong.
Age must always be calculated:
Age = Datediff("y', DOB, Date())

The problem with this is if someone born on january 2015 and we are now in november you will get the age of 9.
You can make it more acurate by:
Age = Datediff("m', DOB, Date())/12

 

[Frothingslosh]

I wound up just keeping this little function around for when I need to deal with dates. It can use any given date, so you can see what age someone will be on a specified date, and takes the birthday issue smig mentioned into account.

Public Function GetAge(ByVal DateOfBirth As Date, _
                       ByVal EventDate As Date) As Integer
 
[COLOR=seagreen]'EventDate is the date you're testing against.  This is most commonly the current date.[/COLOR]
 
    Dim BDay As Date
 
    BDay = DateSerial(Year(EventDate), Month(DateOfBirth), Day(DateOfBirth))
 
    GetAge = DateDiff("yyyy", DateOfBirth, EventDate) + (BDay > EventDate)
 
End Function

You could even do it in one line with this:

DateDiff("yyyy", DateOfBirth, EventDate) + (DateSerial(Year(EventDate), Month(DateOfBirth), Day(DateOfBirth)) > EventDate)

Just make sure to replace EventDate and DateOfBirth with the values you're testing against.

Edit: Hell, smig's one-line version is even more elegant, although I'd recommend running the Int function on it.

Age = Int(DateDiff("m", DOB, Date) / 12)

 

[namliam]

So many roads that lead to a solution

 

[smig]

No age you dont input because it changes every year....

A quick google will teach you a lot
https://www.google.nl/search?hl=en-.....1ac.1.34.heirloom-hp..0.23.1154.hSvfX62UpEQ

One of the quickest way is to calcaulte it on the fly in a query or report using something like
Year(Date()) - Year(DOB) + (format(DOB, "MMDD") < format(Date(), "MMDD"))

This way is wrong from two reasons:
1. Using format() as you did make the date data into a text string.
2. You better don't mess dates as they are numbers. There are special function for dates for a reason (datediff, dateadd...)

If you do it enough times you will get unexpected result at some point.

 

[namliam]

This way is wrong from two reasons:
1. Using format() as you did make the date data into a text string.
2. You better don't mess dates as they are numbers. There are special function for dates for a reason (datediff, dateadd...)

If you do it enough times you will get unexpected result at some point.

You think??? Yes dates are special and dates do require special attention and do trip up a lot of people... I however am one of those that can manipulate time and space and understands it too....

Using format in this case transferring the date to MMDD is a simpel way of finding out if the birthday has already been or not. Since a true simply equates to -1 and a false to 0 the format comparison shall deduct 1 from the difference in years if the birthday this year has not yet passed.

Dont exactly know how when or why, despite me having used this very formula from memory 1000 times, I did reverse the two format functions somehow...
Year(Date()) - Year(DOB) + (format(Date(), "MMDD") < format(DOB, "MMDD") )

 

[Uncle Gizmo]

>>> I am one of those that can manipulate time and space <<< that's a prerequisite for an MS Access developer!

I always get my stuff in a mucking fuddle.

 

[rossjackson01]

Thank you to all. Excellent replies. I have bookmarked the entries and will attempt as requested.

Gosh, Access and this forum is good, isn't it?

Regards

Ross