Getting Age of customer based on Birthday

[Guest]

I have a query with two tables and two fields that make up this calculation
The Birthdate field is a Date/Time datatype and the Age of Customer is a
Number datatype and will hold the calculation.

Example..

Table A....................Table B
AgeOfCustomer.......Birthdate

I need to fill in the the Age of the Customer field based on the Birthdate
of the customer. Where do I start or what is the expression etc for this
calculation.

 

[fredg]

I have a query with two tables and two fields that make up this calculation
The Birthdate field is a Date/Time datatype and the Age of Customer is a
Number datatype and will hold the calculation.

Example..

Table A....................Table B
AgeOfCustomer.......Birthdate

I need to fill in the the Age of the Customer field based on the Birthdate
of the customer. Where do I start or what is the expression etc for this
calculation.

In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control in a form or in a
report:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

This Age computation should NOT be stored in any table.
Just compute it and display it on a form or report, as needed.

 

[Guest]

hi,

Here is a sample query. In it, we use the DateDiff() function. You can
adapt it to your needs, particularly your table and column names. Pay close
attention to the column named "AGE" in this sample query. This will
hopefully help you in calculating the age in your case:

SELECT test.name, test.birthdate,
IIf(DateDiff("yyyy",[birthdate],Now())=0,DateDiff("m",[birthdate],Now()) & "
months",IIf(DateDiff("yyyy",[birthdate],Now())<>0,DateDiff("yyyy",[birthdate],Now())
& " years",IIf(IsNull(DateDiff("yyyy",[birthdate],Now())),"NULL"))) AS AGE
FROM test;

Hope this helps,
geebee

 

[Guest]

Thank you very much! For the 1st part of this calculation it worked
perfectly! Can I ask you the 2nd half? What I need last is to get to the
correct LE_VALUE for that customer. I have 3 fields in the same table whcih
are all Number datatypes.

Now that I have the age of the customer I need to subtract 10 from the age
in turn will give me the correct data in the BENE_AGE fieled then will give
me the correct and final data in the LE_VALUE FIELD

EXAMPLE

FIELDS

PART_AGE_________BENE_AGE_____________LE_VALUE

85 23 44.3
85 16 47.3
85 75 34.9


notice the Participant has three age 85s? I need to subtract 10 for the age
which in turn will give me 75 as it reads above in turn will give me the
value I am looking for at 34.9.

How do I do this?

I have a query with two tables and two fields that make up this calculation
The Birthdate field is a Date/Time datatype and the Age of Customer is a
Number datatype and will hold the calculation.

Example..

Table A....................Table B
AgeOfCustomer.......Birthdate

I need to fill in the the Age of the Customer field based on the Birthdate
of the customer. Where do I start or what is the expression etc for this
calculation.

In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control in a form or in a
report:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

This Age computation should NOT be stored in any table.
Just compute it and display it on a form or report, as needed.