How can I calcute age in Access 2003?

[Guest]

I have a database in one form wich includes the field "birthdate" and "day of
intervention", and I want to calculate the age, for each record, using those
two dates (wich are different for every record). How can I do that? I tried
everything but nothing works...
Thanks

 

[fredg]

I have a database in one form wich includes the field "birthdate" and "day of
intervention", and I want to calculate the age, for each record, using those
two dates (wich are different for every record). How can I do that? I tried
everything but nothing works...
Thanks

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

Directly as the control source of an unbound control:
=DateDiff("yyyy",[DOB],[DayOfIntervention])-IIf(Format([DOB],
"mmdd")>Format([DayOfIntervention],"mmdd"),1,0)

Where [DOB] is the birthdate field.

You do know, I hope, that this Age computation should NOT be stored
in any table. Just compute it and display it on a form or report, as
needed.

 

[Guest]

Well, I just tried both (in query and as the control source) but it doesn't
work...

There are error messages:

In a query: Invalid SQL Instruction. DELETE, INSERT, PROCEDURE, SELECT
or UPDATE expected

Wen I put it as the control source: "you omitted an operand or operator,
you entered an invalid character or comma, or you entered text without
surrounding it in quotation marks"

I don't know what else to do...

P.S. Age is not stored in the table
----------------------------

"fredg" escreveu:

I have a database in one form wich includes the field "birthdate" and "day of
intervention", and I want to calculate the age, for each record, using those
two dates (wich are different for every record). How can I do that? I tried
everything but nothing works...
Thanks

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

Directly as the control source of an unbound control:
=DateDiff("yyyy",[DOB],[DayOfIntervention])-IIf(Format([DOB],
"mmdd")>Format([DayOfIntervention],"mmdd"),1,0)

Where [DOB] is the birthdate field.

You do know, I hope, that this Age computation should NOT be stored
in any table. Just compute it and display it on a form or report, as
needed.

 

[fredg]

Well, I just tried both (in query and as the control source) but it doesn't
work...

There are error messages:

In a query: Invalid SQL Instruction. DELETE, INSERT, PROCEDURE, SELECT
or UPDATE expected

Wen I put it as the control source: "you omitted an operand or operator,
you entered an invalid character or comma, or you entered text without
surrounding it in quotation marks"

I don't know what else to do...

P.S. Age is not stored in the table
----------------------------

"fredg" escreveu:

I have a database in one form wich includes the field "birthdate" and "day of
intervention", and I want to calculate the age, for each record, using those
two dates (wich are different for every record). How can I do that? I tried
everything but nothing works...
Thanks

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

Directly as the control source of an unbound control:
=DateDiff("yyyy",[DOB],[DayOfIntervention])-IIf(Format([DOB],
"mmdd")>Format([DayOfIntervention],"mmdd"),1,0)

Where [DOB] is the birthdate field.

You do know, I hope, that this Age computation should NOT be stored
in any table. Just compute it and display it on a form or report, as
needed.

Neither do I, because while you tell us it the error messages you get,
you haven't posted the exact expression you have used in the query or
the control source.

Are you aware that each of those expressions should be all on one
line? Newsreaders split lines when they are too long.

Is the birth date field ([DOD]) a Date/Time datatype?
Is the [DayOfIntervention] a Date/Time datatype?
Did you change [DOD] to the name of your actual birth date field?

Copy and paste into a reply the exact query SQL
Copy and paste into a reply the exact control source expression.

 

[John W. Vinson]

In a query: Invalid SQL Instruction. DELETE, INSERT, PROCEDURE, SELECT
or UPDATE expected

I think what Fred intended is that you put that expression into a vacant Field
cell in the query design grid. It appears that you tried to use it as the SQL
of a complete query.

Wen I put it as the control source: "you omitted an operand or operator,
you entered an invalid character or comma, or you entered text without
surrounding it in quotation marks"

You evidently made a mistake in the control source.

I don't know what else to do...

Copy and paste the exact expression from the form control to a message here.

John W. Vinson [MVP]

 

[Guest]

Well,

- the birth date field ([DOD]) and the [DayOfIntervention] are Date/Time
datatype
- I changed [DOD] to the name of my actual birth date field: the field's
name is "Data de Nascimento", and I tryed with [DatadeNascimento] and [Data
de Nascimento], but didn't worked
- I wrote all expressions in one single line

This is what I wrote ("Idade" is the name of the "Age" field,
"DatadeNascimento" is "DOB" and "Data" is the "DayOfIntervention"):

--------------------------
1 - In a Query:
Idade: DateDiff("yyyy", [DatadeNascimento], [Data]) - IIF(Format
([DatadeNascimento], "mmdd") Format([Data], "mmdd"), 1, 0)

or:
Idade: DateDiff("yyyy", [DatadeNascimento], [Data]) - IIF(Format
([DatadeNascimento], "mmdd") > Format([Data], "mmdd"), 1, 0)


- I wrote that as a SQL, in a single line, and didn't worked.
- I wrote that in a vacant Field cell in the query design grid
- Both situations showed the error message "Invalid SQL Instruction. DELETE,
INSERT, PROCEDURE, SELECT or UPDATE expected", and it selected the comma
after the "yyyy" (like if it was the responsible for the error)... and if I
delete the comma, it says "The expression you entered contains invalid
syntax. You may have entered an operand without an operator" and it selects
[DatadeNascimento]

----------------------------
2 - As the control source of an unbound control:
=DateDiff("yyyy",[DatadeNascimento],[Data])-IIf(Format([DatadeNascimento],"mmdd")>Format([Data],"mmdd"),1,0)

-Error message: "The expression you entered contains invalid syntax. You
omitted an operand or operator, you entered an invalid character or comma, or
you entered text without surrounding it in quotation marks"


Can you help me with this?...

Thanks

 

[John W. Vinson]

Well,

- the birth date field ([DOD]) and the [DayOfIntervention] are Date/Time
datatype
- I changed [DOD] to the name of my actual birth date field: the field's
name is "Data de Nascimento", and I tryed with [DatadeNascimento] and [Data
de Nascimento], but didn't worked
- I wrote all expressions in one single line

This is what I wrote ("Idade" is the name of the "Age" field,
"DatadeNascimento" is "DOB" and "Data" is the "DayOfIntervention"):

--------------------------
1 - In a Query:
Idade: DateDiff("yyyy", [DatadeNascimento], [Data]) - IIF(Format
([DatadeNascimento], "mmdd") Format([Data], "mmdd"), 1, 0)

or:
Idade: DateDiff("yyyy", [DatadeNascimento], [Data]) - IIF(Format
([DatadeNascimento], "mmdd") > Format([Data], "mmdd"), 1, 0)

Blanks are meaningful. The text strings "Datadenascimento" and "Data de
nascimento" are *different text strings*, and as far as Access is concerned,
have nothing whatsoever to do with each other.

Do you have a field in your table named Data? What's the Italian name of the
built-in Access function which in English is Date()?

Try

Idade: DateDiff("yyyy", [Data de Nascimento],Date()) - IIF(Format
([Data de Nascimento], "mmdd") Format(Date(), "mmdd"), 1, 0)

Note that Date no square brackets and has () after it to tell Access that it's
a function not a fieldname - you may need to use Data() instead of Date().

And I did NOT suggest that you "write this as SQL"; I suggested that you put
it into the query as a calculated field, by putting the expression in a vacant
FIELD cell in the query grid.

- I wrote that as a SQL, in a single line, and didn't worked.
- I wrote that in a vacant Field cell in the query design grid
- Both situations showed the error message "Invalid SQL Instruction. DELETE,
INSERT, PROCEDURE, SELECT or UPDATE expected", and it selected the comma
after the "yyyy" (like if it was the responsible for the error)... and if I
delete the comma, it says "The expression you entered contains invalid
syntax. You may have entered an operand without an operator" and it selects
[DatadeNascimento]

That suggests that you put the expression as the entire SQL string. Won't work
because it's not valid SQL!

----------------------------
2 - As the control source of an unbound control:
=DateDiff("yyyy",[DatadeNascimento],[Data])-IIf(Format([DatadeNascimento],"mmdd")>Format([Data],"mmdd"),1,0)

-Error message: "The expression you entered contains invalid syntax. You
omitted an operand or operator, you entered an invalid character or comma, or
you entered text without surrounding it in quotation marks"

=DateDiff("yyyy",[Data de Nascimento],Date()) - IIf(Format([Data de
Nascimento],"mmdd") > Format(Date(),"mmdd"),1,0)


John W. Vinson [MVP]

 

[Guest]

Blanks are meaningful. The text strings "Datadenascimento" and "Data de

nascimento" are *different text strings*, and as far as Access is concerned,
have nothing whatsoever to do with each other.

I tried with "Datadenascimento" and "Data de nascimento" and didn't work...

Do you have a field in your table named Data? What's the Italian name of the
built-in Access function which in English is Date()?

Yes, I have a field in my table named "Data" and another named "Data de
Nascimento". My Access is in english, so I guess Date() should work.....

Try

Idade: DateDiff("yyyy", [Data de Nascimento],Date()) - IIF(Format
([Data de Nascimento], "mmdd") Format(Date(), "mmdd"), 1, 0)

I tried....Didn't work....Same error message...

And I did NOT suggest that you "write this as SQL"; I suggested that you put
it into the query as a calculated field, by putting the expression in a vacant
FIELD cell in the query grid.

Yes. I just said that I tried both ways (SQL and vacant field cell) and
didn't work

=DateDiff("yyyy",[Data de Nascimento],Date()) - IIf(Format([Data de
Nascimento],"mmdd") > Format(Date(),"mmdd"),1,0)

Didn't work also.... What else to do?...

Thanks

 

[John W. Vinson]

Didn't work also.... What else to do?...

Thanks

Maybe your references are messed up - that will interfere with Date() and
DateDiff() and other functions. Try typing Ctrl-G to open the VBA editor, and
select Tools... References from the menu. If any are marked MISSING, uncheck
that line; close and open Access; and if it's a reference that you actually
need, recheck it. You may have to locate the file that the line refers to if
it's actually missing (rather than Access just losing track of it, which
unchecking and checking again should fix).


John W. Vinson [MVP]

 

[Bob Quintal]

Well, I just solved my problem... I used the following
expression for the control source of the age (Idade) field:

=Int(([Data]-[Data de Nascimento])/365,2)

It gives the age with an error of just one day (when Data =
the day before birthday, it's like Data = Birthday). But it's
better than nothing....

Thanks

The function linked to below doesn't have this error.

http://allenbrowne.com/func-08.html

 

[Guest]

Well, I just solved my problem... I used the following expression for the
control source of the age (Idade) field:

=Int(([Data]-[Data de Nascimento])/365,2)

It gives the age with an error of just one day (when Data = the day before
birthday, it's like Data = Birthday). But it's better than nothing....

Thanks

 

[Douglas J. Steele]

If that works, then

DateDiff("yyyy", [Data de Nascimento], [Data]) - IIf(Format([Data de
Nascimento], "mmdd") > Format([Data], "mmdd"), 1, 0)

should work, and not have the error you describe.