Another Birthday Question (1 Viewer)

[Navyguy]

I am using the qry below to determine someone's birthday, and it works good from my standpoint although it is not as elaborate as some of the other B-Day code I have seen in this forum.

SELECT Year([DOB]) AS [DOB Year], Month([DOB]) AS [DOB Month], Day([DOB]) AS [DOB Day], [Tbl-Personnel].[DOB], DateDiff("yyyy",[DOB],Date()) AS Age1, DatePart("y",[DOB])>DatePart("y",Date()) AS Age2, [Age1]+[Age2] AS Age, [Tbl-Personnel].Surname AS Surname, [Tbl-Personnel].[First Name] AS [First Name], [Tbl-Personnel].[Gender] AS [Gender], [Tbl-Personnel].[Group]
FROM [Tbl-Personnel]
ORDER BY Month([DOB]), Day([DOB]), [Tbl-Personnel].Surname, [Tbl-Personnel].[First Name];

My questions are:

1. On a form I would like to enter someone's birth date and have their age automatically display in a box called CurrentAge. That age need to be kept for reporting purposes later so it cannot be continuously calculated. This seems easy enough, but I having problems getting it to work.

2. On the same form I would also like to calculate someone's age from a specific date. So I would like to know how old Joe would be after 01 Sept 04 as an example after his birthday was inputted into the DOB field.

So in essence what I would like is two fields to be auto completed (CurrentAge and FutureAge) based on the entry in the DOB field that are all on the same form.

As always Thanks for your help.

 

[Mile-O]

1. On a form I would like to enter someone's birth date and have their age automatically display in a box called CurrentAge. That age need to be kept for reporting purposes later so it cannot be continuously calculated. This seems easy enough, but I having problems getting it to work.

The age does not need to be kept. On your report, just bring the Date of Birth into the underlying query.

2. On the same form I would also like to calculate someone's age from a specific date. So I would like to know how old Joe would be after 01 Sept 04 as an example after his birthday was inputted into the DOB field.

This will do both:

Public Function Age(StartDate As Date, Optional EndDate As Date) As Integer 
    If IsMissing(EndDate) Then
        Age = DateDiff("yyyy", StartDate, Now()) + _ 
            Int(Format(Now(), "mmdd") < Format(StartDate, "mmdd")) 
    Else
        Age = DateDiff("yyyy", StartDate, EndDate) + _ 
            Int(Format(EndDate, "mmdd") < Format(StartDate, "mmdd")) 
    End If
End Function

 

[Navyguy]

Thanks Mile

I will give it a try. I was wondering, if I wanted to know how many XX year olds I had entered and I was doing it a year later, by not storing their age and using the Now(), would that not skew the results? Someone that was 14 this year would be 15 next year when I was doing the comparison report, but I maybe want to know how many 14 year olds that entered last year and compar to this year.

Is the StartDate my specific date?

I am sure I will have more questions.

 

[Mile-O]

Look at the two arguments I provided in the function:

(StartDate As Date, Optional EndDate As Date)

StartDate is a required argument and this represent the date of birth.

EndDate, however, is optional meaning that you don't have to include it - if you don't then it will calculate actual age from the StartDate; and if you do then it calculates the age between the two dates:


For actual age:

MyField: Age([DOB])


For age at specific time:

MyField: Age([DOB], [CutOffDate])

 

[Navyguy]

No Clue what I am doing!!!

Mile

I have no clue what I am doing. Need some guidance.

 

[Mile-O]

I have no clue what I am doing.

You are right.

The changes:

• Changed the field names to remove spaces;
• Changed object names to a more recognised standard;
• Changed DOB field from Text to Date - why did you have this as Text anyway?
• Added new table for Groups and connected them on GroupID;
• As having Male or Female in a field takes up a hell of a lot more than a number, I changed the Gender field to a Number field and made Male = 1, Female = 2
• In the query, I deleted everything you had and simply added the function
• On the form, I changed the ControlSource to demonstrate the Optional argument

 

[Navyguy]

Thank you Mile...

Some of the things that you changed I already have, but was trying to do it from memory as I am not home right now where my working copy is, ie better object names and the DOB as date vs text etc.

I will take some time to look in detail what you did and try to sort it out and get a handle on it. Maybe easier after I get some sleep.

Thanks again!!!

AKA "Does Not Have A Clue"

 

[Pat Hartman]

I changed the Gender field to a Number field and made Male = 1, Female = 2

I would have made this Female = 1, Male = 2

 

[Mile-O]

I would have made this Female = 1, Male = 2

I admit, my choice of female = 2, male = 1, was more fantasy than anything else.

 

[Navyguy]

I admit, my choice of female = 2, male = 1, was more fantasy than anything else.

I concur!!

I have been looking at the sample module that you posted Mile...I have some questions:

Where does dteStart and dteEnd come from? I understand that it is startdate and enddate, but where is that information in the DB?

Does the module run every time it sees the Age field on a form?

Thanks for the explanations.

 

[Mile-O]

dteStart was the DOB and dteEnd, if included, was just any other date. In the sample I think I just hardcoded a date.

 

[Navyguy]

I must be missing something pretty simple...go figure...

You are right about the dteStart being the DOB, but how does it (the module I guess) know that? What if I had another date say for registration or testing date. How does the module know to look at the DOB?

Does this have to do with the field Age and the module named Age being the same?

 

[Mile-O]

Public Function Age(StartDate As Date, Optional EndDate As Date) As Integer 
    If IsMissing(EndDate) Then
        Age = DateDiff("yyyy", StartDate, Now()) + _ 
            Int(Format(Now(), "mmdd") < Format(StartDate, "mmdd")) 
    Else
        Age = DateDiff("yyyy", StartDate, EndDate) + _ 
            Int(Format(EndDate, "mmdd") < Format(StartDate, "mmdd")) 
    End If
End Function

The function doesn't care about what is DOB, or whatver. You could put DateOfEmployement and DateOfResignation through to the function rather than DOB and today's date.

Because you "pass" DOB to the function, you are effectively sending the date into the function (along with another optional date) and the logic decides on an outcome depending on what is given to it.

 

[Navyguy]

Thanks Mile

I still don't understand though...I have been reading up on how modules work but I still don't get it. Can you point me to some material to research so I quit asking stupid questions? I am sure your reply was totally accurate but you might as well have wrote it in another language.

I think I need to understand how the module works "mechanically" and then I will perhaps understand your answers.

Something else I don't get, is the controlsource for the form =Age([txtDOB], #whateverdate#). How does it know what to do there? Is that the form calling the module? Is that the same as Age(StartDate as date, EndDate as date)?

I have been reading up on this, but I seem to have some kind of mental bolck on this...I am sure it is not that tough...


Thanks