Age: DateDiff ("yyyy", [BirthDate], Date())

[Apple]

Hi



I am using Access 2007, and am new to Access.

I would like to calculate the Age based on the Date of Birth , which is in dd/mm/yyyy

I use the below expression in Query but I get back #Error for most records; whereas the Age appears correctly for a few records.

Age: DateDiff ("yyyy", [BirthDate], Date())


Thank you

[Ruegen]

use format([BirthDate],"mm/dd/yyyy")

Access does stuff in American date - try that first.

[Apple]

The formula works fine when I copy and paste the Date of Birth onto a new table

Table 1:
1) Name
2) Date of Birth (dd/mm/yyyy)

Table 2:
2) Date of Birth (dd/mm/yyyy)


So my query becomes
1) Name from Table 1
2) Date of Birth (dd/mm/yyyy) from Table 2

However I don't want the Name & Date of Birth (dd/mm/yyyy) to be from 2 separate tables, how do I resolve this issue?

Thank you!

Hi

I am using Access 2007, and am new to Access.

I would like to calculate the Age based on the Date of Birth , which is in dd/mm/yyyy

I use the below expression in Query but I get back #Error for most records; whereas the Age appears correctly for a few records.

Age: DateDiff ("yyyy", [BirthDate], Date())


Thank you

[Ruegen]

The formula works fine when I copy and paste the Date of Birth onto a new table

Table 1:
1) Name
2) Date of Birth (dd/mm/yyyy)

Table 2:
2) Date of Birth (dd/mm/yyyy)


So my query becomes
1) Name from Table 1
2) Date of Birth (dd/mm/yyyy) from Table 2

However I don't want the Name & Date of Birth (dd/mm/yyyy) to be from 2 separate tables, how do I resolve this issue?

Thank you!

Not sure why you have two separate tables - they should be on the one table - you could merge the tables.

Whatever your choice - make a backup first!

Also the reason for formatting dates is any dates get interpreted as US dates so everytime you put in a date value that is not in American you have to format it in the code.

[Apple]

Hi Rugen

Yes the 2 columns are in Table 1. But when I select both columns from the same table the formula
Age: DateDiff ("yyyy", [BirthDate], Date())
don't work.

But when I separate the 2 columns into 2 tables the formula work fine, why is that so?

Thank you

Not sure why you have two separate tables - they should be on the one table - you could merge the tables.

Whatever your choice - make a backup first!

Also the reason for formatting dates is any dates get interpreted as US dates so everytime you put in a date value that is not in American you have to format it in the code.

[Ruegen]

I think you have made a merged field in the query yes? That would be a custom field that pulls data from two fields to make one. That is a calculated field. You can't run the method on the calculated field because it's not a date field - it's a merged calculated field of two.

[Apple]

No I didn't merge the two columns. Please see image of my query. Thanks!

Hi Rugen

Yes the 2 columns are in Table 1. But when I select both columns from the same table the formula
Age: DateDiff ("yyyy", [BirthDate], Date())
don't work.

But when I separate the 2 columns into 2 tables the formula work fine, why is that so?

Thank you

[Ruegen]

No I didn't merge the two columns. Please see image of my query. Thanks!

oh dear...

You've put spaces in the names of your fields.


Date of Birth (dd/mm/yyyy)

should be a field called

BoysDOB or DateOfBirth - and anything like (dd/mm/yyyy) should be put in the description column when making the field

also the date of birth isn't formatted like I suggested to mm/dd/yyyy

format() should be wrapped around your date field and it isn't.

I believe you have broken some commandments.

http://access.mvps.org/access/tencommandments.htm

also your table should stick to one word and again not separated by spaces and add the type before it is a good suggestion too.

i.e.

tblBoys

frmBoys

qryBoys

etc

You pretty much broke commandment number 3.

also Full Name shouldn't be an ID and primary key

you should have an ID field which is an autonumber field (the only one you can have)

and that should be the primary.

If you have the First Name & Last Name fields you should never need a third field - you can always concatenate the two fields whenever you need the name in full

FirstName & " " & LastName

ideally you want to put the table before the field name in case you have a table of girls (girlFirstName) but really should should have one with "students" and place all genders in the same table and differentiate them by gender with a field boy/girl option - that can be a bool (yes/no) or another table selection (number field to represent the other table ID).

How far into this are you because you may want to do a little reading to save you a lot of headache down the track...?

[Ruegen]

format([Date of Birth (dd/mm/yyyy)],"mm/dd/yyyy") may be the fix, you might need to drag in that field and sit it before your custom field Age:

[Ruegen]

One last tip, if any of your fields are related to a table say, Nationality, then it would be an number field tied to the ID field of the related table. This means you would have a table called tblNationalities (notice I use plural as each row in the table is a nationality and grouped into a table called Nationalities - stick to this protocol for programming). then each row you could have the ID field first then a text field for the nationality second - this would mean you can select a number for the nationality. For repeated tasks like selecting a students nationality you could then use the table in a combo box (on a form) and that excludes you having to 'type' each nationality because you could have a set of pre-determined ones to choose from.

[Apple]

1) changed all the names to without spacing

I tried including a field "Expr1: Format([DOB_dd_mm_yyyy],"mm/dd/yyyy")" but I get back error for this column.

Thank you

format([Date of Birth (dd/mm/yyyy)],"mm/dd/yyyy") may be the fix, you might need to drag in that field and sit it before your custom field Age:

[Ruegen]

1) changed all the names to without spacing

I tried including a field "Expr1: Format([DOB_dd_mm_yyyy],"mm/dd/yyyy")" but I get back error for this column.

Thank you

Is the field a date field?

Can you drag in the field before the custom field? Does that change anything if you do?

make sure there are no surrounding " in your field as it should look like this

Expr1: Format([DOB_dd_mm_yyyy],"mm/dd/yyyy")

[Apple]

I didn't use a date field for "DOB_dd_mm_yyyy". It is only a text field.

Thanks

Is the field a date field?

Can you drag in the field before the custom field? Does that change anything if you do?

make sure there are no surrounding " in your field as it should look like this

Expr1: Format([DOB_dd_mm_yyyy],"mm/dd/yyyy")

[Ruegen]

I didn't use a date field for "DOB_dd_mm_yyyy". It is only a text field.

Thanks

ok. Take a deep breath. Now sigh. Relax.

You have found your problem.

Since it is a text field it will not behave as a date field. You could backup your database, copy the your text field (as a column) paste/insert and rename the copied field the convert it to a date field. I'm not sure but that copied column of text dates may get converted into date fields.

But to do it correctly - it should have been a date field to begin with.

[Apple]

1) "copy the your text field (as a column) paste/insert and rename the copied field the convert it to a date field. I'm not sure but that copied column of text dates may get converted into date fields."

2) Age: DateDiff ("yyyy", [BirthDate], Date())

Both works.

Thanks a lot for all your replies!

ok. Take a deep breath. Now sigh. Relax.

You have found your problem.

Since it is a text field it will not behave as a date field. You could backup your database, copy the your text field (as a column) paste/insert and rename the copied field the convert it to a date field. I'm not sure but that copied column of text dates may get converted into date fields.

But to do it correctly - it should have been a date field to begin with.