newbie question...

[Jim]

Hello,

I am relatively new to access (using access 2002), and I have been given a
database to work with. It is a membership list, with various information
about the members. One of the fields is the member's age. The field could
contain a numerical value, text (i.e., "senior") or be blank. My question
is, is there a quick and relatively easy way that I can automatically
increment the year if it's a numerical value?

Thanks in advance for any help.

Jim

 

[Jerry Whittle]

Is there a date of birth field in the table? If so, there shouldn't even be
an age field as you could compute the person's age as needed in queries,
forms, and reports. Same thing goes if there are catagories like 'senior' or
'junior'.

Now you might think that just subtracting a person's birth year from the
current year would work, but it could be up to a year off. For that I highly
recommend Allen Browne's Age function available at:
http://allenbrowne.com/func-08.html

 

[Pat Hartman]

Rather than storing an age which might be out of date the very next day, you
should be storing DOB. That way the age is always correct.

Given the problem you have, you will need to convert the text string to a
numeric value before you can increment it. So, try something like
IIf(IsNumeric([YourField]), cInt([YourField]) + 1, pYourField])

Use this as the "to" value in an update query. Don't forget the square
brackets or Access will "helpfully" surround the field name with quotes
thereby turning it into a text string and invalidating the expression.

 

[Jim]

Jerry & Pat,

thanks so much for the prompt responses. Unfortunately, there are no DOBs in
this table. And When I say I am a newbie to access, I really mean it <g>. I
don't know how to apply a query, even if that would help in this case, which
it doesn't look like it would. It looks like I'm in a tough spot. Could I
sort the ages and do a find/replace?

Also, is there a beginner's reference book for folks like myself that you
could recommend?

Thanks again.

Jim

Rather than storing an age which might be out of date the very next day, you
should be storing DOB. That way the age is always correct.

Given the problem you have, you will need to convert the text string to a
numeric value before you can increment it. So, try something like
IIf(IsNumeric([YourField]), cInt([YourField]) + 1, pYourField])

Use this as the "to" value in an update query. Don't forget the square
brackets or Access will "helpfully" surround the field name with quotes
thereby turning it into a text string and invalidating the expression.

Hello,

I am relatively new to access (using access 2002), and I have been given a
database to work with. It is a membership list, with various information
about the members. One of the fields is the member's age. The field could
contain a numerical value, text (i.e., "senior") or be blank. My question
is, is there a quick and relatively easy way that I can automatically
increment the year if it's a numerical value?

Thanks in advance for any help.

Jim

 

[Jerry Whittle]

If there's no Date of Birth field, how can you tell how old these people are?
What if the people who gave you the database forgot to update them? On what
day of the year do you change a person's age? I was born in June. If you
change it in January, you've added a year too soon. In December it would be a
few months too late.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry & Pat,

thanks so much for the prompt responses. Unfortunately, there are no DOBs in
this table. And When I say I am a newbie to access, I really mean it <g>. I
don't know how to apply a query, even if that would help in this case, which
it doesn't look like it would. It looks like I'm in a tough spot. Could I
sort the ages and do a find/replace?

Also, is there a beginner's reference book for folks like myself that you
could recommend?

Thanks again.

Jim

Rather than storing an age which might be out of date the very next day, you
should be storing DOB. That way the age is always correct.

Given the problem you have, you will need to convert the text string to a
numeric value before you can increment it. So, try something like
IIf(IsNumeric([YourField]), cInt([YourField]) + 1, pYourField])

Use this as the "to" value in an update query. Don't forget the square
brackets or Access will "helpfully" surround the field name with quotes
thereby turning it into a text string and invalidating the expression.

Hello,

I am relatively new to access (using access 2002), and I have been given a
database to work with. It is a membership list, with various information
about the members. One of the fields is the member's age. The field could
contain a numerical value, text (i.e., "senior") or be blank. My question
is, is there a quick and relatively easy way that I can automatically
increment the year if it's a numerical value?

Thanks in advance for any help.

Jim

 

[John W. Vinson]

Could I
sort the ages and do a find/replace?

You could use a complicated Update query...

but I agree with Jerry. Ages simply SHOULD NOT EXIST in a database table.
They're one type of data which you can absolutely guarantee will be *wrong*
for every single record in the table within one year.

What use do you make of these ages? How are they collected? You say you have a
mix of numeric and text ages: what happens to "Middle-Aged" in one year, or in
ten?

You will really need to bite the bullet and - if ages accurate to one year are
needed for your application - create a date of birth field and populate it.
You can use an Update query to do so; if (for instance) you want everyone to
have January 1 as a birthdate and you trust the numeric ages that you have in
the table now, you could add a DOB date/time field and update it to

DateSerial(Year(Date()) - [Age], 1, 1)

by including a calculated field in your query

RealAge: IsNumeric([Age])

with a criterion of True.

John W. Vinson [MVP]

 

[Jorge Kiss]

TESTE DE ENVIO

If there's no Date of Birth field, how can you tell how old these people
are?
What if the people who gave you the database forgot to update them? On
what
day of the year do you change a person's age? I was born in June. If you
change it in January, you've added a year too soon. In December it would
be a
few months too late.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry & Pat,

thanks so much for the prompt responses. Unfortunately, there are no DOBs
in
this table. And When I say I am a newbie to access, I really mean it <g>.
I
don't know how to apply a query, even if that would help in this case,
which
it doesn't look like it would. It looks like I'm in a tough spot. Could I
sort the ages and do a find/replace?

Also, is there a beginner's reference book for folks like myself that you
could recommend?

Thanks again.

Jim

Rather than storing an age which might be out of date the very next
day, you
should be storing DOB. That way the age is always correct.

Given the problem you have, you will need to convert the text string to
a
numeric value before you can increment it. So, try something like
IIf(IsNumeric([YourField]), cInt([YourField]) + 1, pYourField])

Use this as the "to" value in an update query. Don't forget the square
brackets or Access will "helpfully" surround the field name with quotes
thereby turning it into a text string and invalidating the expression.

Hello,

I am relatively new to access (using access 2002), and I have been
given a
database to work with. It is a membership list, with various
information
about the members. One of the fields is the member's age. The field could
contain a numerical value, text (i.e., "senior") or be blank. My question
is, is there a quick and relatively easy way that I can automatically
increment the year if it's a numerical value?

Thanks in advance for any help.

Jim