Age asof June 30, ???? Calculation

[burcher]

I am trying to calculate a persons age as of June 30, [UDSyr]. The [UDSyr] field is entered on the report form in an unbound text box and is used by several other queries. My age calulation was:

UPDATE [UDS Age & Gender Table] SET [UDS Age & Gender Table].Age = DateDiff("yyyy",[DOB],Date());

What's the best way for me to have this sql use 6/30/[UDSyr] instead of Date()

 

[scriverb]

Use the following for your update query:

UPDATE [UDS Age & Gender Table] SET [UDS Age & Gender Table].Age = DateDiff("yyyy",[DOB],Int((DateDiff('d',[DOB],DateSerial(CInt([Enter Year:]),6/30) )/365.25)));

I think this should do it for you. I wasn't sure just where this year value was being stored. Whether it was a value from a form where you are calling this SQL to be run or from a table. The above code just prompts you for the year entry and then the DateSerial builds the date for you.

If you have a form with a control to enter the year value then just substitute the long format reference to the forms control for the DateSerial function.

UPDATE [UDS Age & Gender Table] SET [UDS Age & Gender Table].Age = DateDiff("yyyy",[DOB],Int((DateDiff('d',[DOB], FORMS![[i]yourformname].[controlname[/i]]/365.25)));

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[burcher]

Bob,
I've tried it both ways you have above and I keep getting a syntax error.
My situation is more closely suited towards your 2nd code since I have them enter the year like 2003 or 2004 in the unbound text box [UDSyr] on the [UDS Report Form], then they push a setup button which uses this number to setup the rest of the data. Here's what I tried
UPDATE [UDS Age & Gender Table] SET [UDS Age & Gender Table].Age = DateDiff("yyyy",[DOB],Int((DateDiff('d',[DOB], FORMS![UDS Report Form].[UDSyr]/365.25)));

I can see in your first example where it takes the 6/30th parameter into consideration, but how does it know it in the 2nd?
Thanks

 

[scriverb]

Sorry about the typo:

UPDATE [UDS Age & Gender Table] SET [UDS Age & Gender Table].Age = DateDiff("yyyy",[DOB],Int((DateDiff('d',[DOB],DateSerial( FORMS![[i]yourformname[/i]].[[i]controlname[/i]],6,30)/365.25)));

I think will do it for you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[burcher]

Bob,
I was getting a sytax error, so I added another ) to the end of it and it ran the update, but now it is saying that a person born on 9/8/1969 is -139 years old and a person born on 5/30/1969 is also -139 years old, so not only is the age incorrect it is not using the 6/30 as the age os of that date. The value in the FORMS![UDS Report Form].[UDSyr] was 2004. What do you think is the problem? Did I put the ) in the right place? This is what the sql looks like on my end right now:
UPDATE [UDS Age & Gender Table] SET [UDS Age & Gender Table].Age = DateDiff("yyyy",[DOB],Int((DateDiff('d',[DOB],DateSerial( FORMS![UDS Report Form].[UDSyr],6,30)/365.25))));

 

[scriverb]

Try this one. I am sorry but I put the Age calc in the wrong spot.

UPDATE [UDS Age & Gender Table] SET [UDS Age & Gender Table].Age = Int(( DateDiff('d',[DOB],
DateSerial(CInt(FORMS![[i]yourformname[/i]].[[i]controlname[/i]]),6,30)) /365.25));

This one has the correct number of parens that should match and calc the age property using your Year control value from the form.

Post back with results.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[burcher]

You the man Bob, Worked perfect. I appreciate your help

 

[scriverb]

This was a fun one. Glad that I could help you get it working correctly.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[MichaelRed]

While not a succinct as Bob's statement(s), there should be a procedure [basDOB2Age(Dob As Date, Optional AsOf As Date = -1)] ? somewhere within these fora.

MichaelRed
mlred@verizon.net