I'd like NOT to see something on my form.

[Sue]

I have a date field (for birthdays) on my form but don't know the year of
birth for many of the people I'd like to remember on their birthdays. For
them, I put in the year 1900. But for SOME of my contacts, I'd like to know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or whatever). I
have a second field which calculates age. But I really don't want to see the
age "107". So is there a way to ONLY have the age display if the year is >
1900?

Thanks.

 

[NevilleT]

Hi Sue

Use the on current event and create the following. Assume the text box is
called txtBirthday

if Year(me![txtBirthday]) = 1900 then
me![txtBirthday].Visible = true
else
me![txtBirthday].Visible=false
end if

 

[fredg]

I have a date field (for birthdays) on my form but don't know the year of
birth for many of the people I'd like to remember on their birthdays. For
them, I put in the year 1900. But for SOME of my contacts, I'd like to know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or whatever). I
have a second field which calculates age. But I really don't want to see the
age "107". So is there a way to ONLY have the age display if the year is >
1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[tina]

i don't think that is quite what Sue was after - it shows the "birthday"
control when the year of birth is 1900, and not at any other time.

Sue, i hope that your "field that calculates age" is actually a calculated
control in the form - NOT a field in the underlying table. rule of thumb is
to not store calculated values in table, but rather to calculate them on the
fly as needed. assuming that you *are* using a calculated control on the
form, i'd suggest controlling the "show/no show" in the expression - that
way you don't have to depend on VBA to recalculate it at strategic points.
try something along the lines of

=IIf(Year[BirthdateFieldName])>1900,<put here the calculation you use to get
the age>,Null)

replace BirthdateFieldName with the correct name of the field, of course.
and replace the <> symbols and the text between them with the expression
that calculates age.

hth

Hi Sue

Use the on current event and create the following. Assume the text box is
called txtBirthday

if Year(me![txtBirthday]) = 1900 then
me![txtBirthday].Visible = true
else
me![txtBirthday].Visible=false
end if

I have a date field (for birthdays) on my form but don't know the year of
birth for many of the people I'd like to remember on their birthdays. For
them, I put in the year 1900. But for SOME of my contacts, I'd like to know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or whatever). I
have a second field which calculates age. But I really don't want to see the
age "107". So is there a way to ONLY have the age display if the year is

1900?

Thanks.

 

[Sue]

I'm a bit confused about how to do this.
My date field is called
DOB
The calculation for age is
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
(And yes, it is a calculated control in the form.)
I've tried plugging in your IIf statement a couple of different ways, but
it's not working. I think this is a "user error" (and I'm the user.) Can you
please help? Here's what I've tried (with and without the "=" preceding the
calculation).

=IIf(Year[DOB])>1900,
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))


Thanks for your help, Tina.

i don't think that is quite what Sue was after - it shows the "birthday"
control when the year of birth is 1900, and not at any other time.

Sue, i hope that your "field that calculates age" is actually a calculated
control in the form - NOT a field in the underlying table. rule of thumb
is
to not store calculated values in table, but rather to calculate them on
the
fly as needed. assuming that you *are* using a calculated control on the
form, i'd suggest controlling the "show/no show" in the expression - that
way you don't have to depend on VBA to recalculate it at strategic points.
try something along the lines of

=IIf(Year[BirthdateFieldName])>1900,<put here the calculation you use to
get
the age>,Null)

replace BirthdateFieldName with the correct name of the field, of course.
and replace the <> symbols and the text between them with the expression
that calculates age.

hth

Hi Sue

Use the on current event and create the following. Assume the text box
is
called txtBirthday

if Year(me![txtBirthday]) = 1900 then
me![txtBirthday].Visible = true
else
me![txtBirthday].Visible=false
end if

I have a date field (for birthdays) on my form but don't know the year of
birth for many of the people I'd like to remember on their birthdays. For
them, I put in the year 1900. But for SOME of my contacts, I'd like to know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or whatever). I
have a second field which calculates age. But I really don't want to
see the
age "107". So is there a way to ONLY have the age display if the year
is

1900?

Thanks.

 

[Sue]

When I tried to apply your suggestion, I got the following error message:
"The expression you entered has a function containing the wrong number of
arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))])



Can you help me to properly rephrase this?



Thanks.

Sue

I have a date field (for birthdays) on my form but don't know the year of
birth for many of the people I'd like to remember on their birthdays. For
them, I put in the year 1900. But for SOME of my contacts, I'd like to
know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to see
the
age "107". So is there a way to ONLY have the age display if the year is
1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[Douglas J. Steele]

Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As well,
you don't care about time, so it's better to use Date() than Now(). Also, if
DOB is a control on your form as well as a field in the form's recordset,
you might need to rename the control to something else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I tried to apply your suggestion, I got the following error message:
"The expression you entered has a function containing the wrong number of
arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))])



Can you help me to properly rephrase this?



Thanks.

Sue

I have a date field (for birthdays) on my form but don't know the year
of
birth for many of the people I'd like to remember on their birthdays.
For
them, I put in the year 1900. But for SOME of my contacts, I'd like to
know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to see
the
age "107". So is there a way to ONLY have the age display if the year is

1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[tina]

you're close, hon. try this:

=IIf(Year([DOB])>1900,Null,DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"
mmdd")<Format([DOB],"mmdd")))

the above goes all on one line in the control's ControlSource property,
regardless of line-wrap in this post.
also notice that i changed the Now() function to Date() function. Date()
returns today's date, while Now() returns today's date AND the current time.
you don't need the current time here, it has no bearing on the calculation.
and it's a good idea to get in the habit of choosing the specific function
that best suits your needs in a given situation, because often the
difference between Date() and Now() *will* have a big impact on what you're
doing.

hth

I'm a bit confused about how to do this.
My date field is called
DOB
The calculation for age is
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
(And yes, it is a calculated control in the form.)
I've tried plugging in your IIf statement a couple of different ways, but
it's not working. I think this is a "user error" (and I'm the user.) Can you
please help? Here's what I've tried (with and without the "=" preceding the

calculation).

=IIf(Year[DOB])>1900,

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))


Thanks for your help, Tina.

i don't think that is quite what Sue was after - it shows the "birthday"
control when the year of birth is 1900, and not at any other time.

Sue, i hope that your "field that calculates age" is actually a calculated
control in the form - NOT a field in the underlying table. rule of thumb
is
to not store calculated values in table, but rather to calculate them on
the
fly as needed. assuming that you *are* using a calculated control on the
form, i'd suggest controlling the "show/no show" in the expression - that
way you don't have to depend on VBA to recalculate it at strategic points.
try something along the lines of

=IIf(Year[BirthdateFieldName])>1900,<put here the calculation you use to
get
the age>,Null)

replace BirthdateFieldName with the correct name of the field, of course.
and replace the <> symbols and the text between them with the expression
that calculates age.

hth

Hi Sue

Use the on current event and create the following. Assume the text box
is
called txtBirthday

if Year(me![txtBirthday]) = 1900 then
me![txtBirthday].Visible = true
else
me![txtBirthday].Visible=false
end if

:

I have a date field (for birthdays) on my form but don't know the

year
of

birth for many of the people I'd like to remember on their birthdays. For
them, I put in the year 1900. But for SOME of my contacts, I'd like

to
know

their age (i.e. for "special" birthdays, like sweet 16 or 21 or whatever). I
have a second field which calculates age. But I really don't want to
see the
age "107". So is there a way to ONLY have the age display if the year
is

1900?

Thanks.

 

[tina]

Doug, will the Me keyword work outside of a VBA module? i don't think i've
ever seen that used before in an expression in a calculated control. tia,
tina

Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd
")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As well,
you don't care about time, so it's better to use Date() than Now(). Also, if
DOB is a control on your form as well as a field in the form's recordset,
you might need to rename the control to something else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date
(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I tried to apply your suggestion, I got the following error message:
"The expression you entered has a function containing the wrong number of
arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
])

Can you help me to properly rephrase this?



Thanks.

Sue

On Tue, 1 May 2007 22:58:11 -0400, Sue wrote:

I have a date field (for birthdays) on my form but don't know the year
of
birth for many of the people I'd like to remember on their birthdays.
For
them, I put in the year 1900. But for SOME of my contacts, I'd like to
know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to see
the
age "107". So is there a way to ONLY have the age display if the year is

1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[Douglas J. Steele]

I believe so, but to be honest, I didn't test.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, will the Me keyword work outside of a VBA module? i don't think i've
ever seen that used before in an expression in a calculated control. tia,
tina

Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd
")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As well,
you don't care about time, so it's better to use Date() than Now(). Also, if
DOB is a control on your form as well as a field in the form's recordset,
you might need to rename the control to something else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date
(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I tried to apply your suggestion, I got the following error message:
"The expression you entered has a function containing the wrong number of
arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
])

Can you help me to properly rephrase this?



Thanks.

Sue



On Tue, 1 May 2007 22:58:11 -0400, Sue wrote:

I have a date field (for birthdays) on my form but don't know the
year
of
birth for many of the people I'd like to remember on their birthdays.
For
them, I put in the year 1900. But for SOME of my contacts, I'd like
to
know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to see
the
age "107". So is there a way to ONLY have the age display if the year is

1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[Sue]

Nope - no ages at all now, even for those whose year is know.
Grrrr.
Will I EVER get this?

you're close, hon. try this:

=IIf(Year([DOB])>1900,Null,DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"
mmdd")<Format([DOB],"mmdd")))

the above goes all on one line in the control's ControlSource property,
regardless of line-wrap in this post.
also notice that i changed the Now() function to Date() function. Date()
returns today's date, while Now() returns today's date AND the current
time.
you don't need the current time here, it has no bearing on the
calculation.
and it's a good idea to get in the habit of choosing the specific function
that best suits your needs in a given situation, because often the
difference between Date() and Now() *will* have a big impact on what
you're
doing.

hth

I'm a bit confused about how to do this.
My date field is called
DOB
The calculation for age is
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
(And yes, it is a calculated control in the form.)
I've tried plugging in your IIf statement a couple of different ways, but
it's not working. I think this is a "user error" (and I'm the user.) Can you
please help? Here's what I've tried (with and without the "=" preceding the

calculation).

=IIf(Year[DOB])>1900,

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))


Thanks for your help, Tina.

i don't think that is quite what Sue was after - it shows the "birthday"
control when the year of birth is 1900, and not at any other time.

Sue, i hope that your "field that calculates age" is actually a calculated
control in the form - NOT a field in the underlying table. rule of
thumb
is
to not store calculated values in table, but rather to calculate them
on
the
fly as needed. assuming that you *are* using a calculated control on
the
form, i'd suggest controlling the "show/no show" in the expression - that
way you don't have to depend on VBA to recalculate it at strategic points.
try something along the lines of

=IIf(Year[BirthdateFieldName])>1900,<put here the calculation you use
to
get
the age>,Null)

replace BirthdateFieldName with the correct name of the field, of course.
and replace the <> symbols and the text between them with the
expression
that calculates age.

hth


Hi Sue

Use the on current event and create the following. Assume the text
box
is
called txtBirthday

if Year(me![txtBirthday]) = 1900 then
me![txtBirthday].Visible = true
else
me![txtBirthday].Visible=false
end if

:

I have a date field (for birthdays) on my form but don't know the year
of
birth for many of the people I'd like to remember on their
birthdays.
For
them, I put in the year 1900. But for SOME of my contacts, I'd like to
know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to
see
the
age "107". So is there a way to ONLY have the age display if the
year
is

1900?

Thanks.

 

[Sue]

OH WAIT -
I bet I know the problem.

I'm using a union query to combine the anniversary & birthday dates - here's
the union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;

Now what should I do?

you're close, hon. try this:

=IIf(Year([DOB])>1900,Null,DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"
mmdd")<Format([DOB],"mmdd")))

the above goes all on one line in the control's ControlSource property,
regardless of line-wrap in this post.
also notice that i changed the Now() function to Date() function. Date()
returns today's date, while Now() returns today's date AND the current
time.
you don't need the current time here, it has no bearing on the
calculation.
and it's a good idea to get in the habit of choosing the specific function
that best suits your needs in a given situation, because often the
difference between Date() and Now() *will* have a big impact on what
you're
doing.

hth

I'm a bit confused about how to do this.
My date field is called
DOB
The calculation for age is
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
(And yes, it is a calculated control in the form.)
I've tried plugging in your IIf statement a couple of different ways, but
it's not working. I think this is a "user error" (and I'm the user.) Can you
please help? Here's what I've tried (with and without the "=" preceding the

calculation).

=IIf(Year[DOB])>1900,

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))


Thanks for your help, Tina.

i don't think that is quite what Sue was after - it shows the "birthday"
control when the year of birth is 1900, and not at any other time.

Sue, i hope that your "field that calculates age" is actually a calculated
control in the form - NOT a field in the underlying table. rule of
thumb
is
to not store calculated values in table, but rather to calculate them
on
the
fly as needed. assuming that you *are* using a calculated control on
the
form, i'd suggest controlling the "show/no show" in the expression - that
way you don't have to depend on VBA to recalculate it at strategic points.
try something along the lines of

=IIf(Year[BirthdateFieldName])>1900,<put here the calculation you use
to
get
the age>,Null)

replace BirthdateFieldName with the correct name of the field, of course.
and replace the <> symbols and the text between them with the
expression
that calculates age.

hth


Hi Sue

Use the on current event and create the following. Assume the text
box
is
called txtBirthday

if Year(me![txtBirthday]) = 1900 then
me![txtBirthday].Visible = true
else
me![txtBirthday].Visible=false
end if

:

I have a date field (for birthdays) on my form but don't know the year
of
birth for many of the people I'd like to remember on their
birthdays.
For
them, I put in the year 1900. But for SOME of my contacts, I'd like to
know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to
see
the
age "107". So is there a way to ONLY have the age display if the
year
is

1900?

Thanks.

 

[Sue]

OH WAIT -
I bet I know the problem.

I'm using a union query to combine the anniversary & birthday dates - here's
the union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;


Now what should I do?

Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As well,
you don't care about time, so it's better to use Date() than Now(). Also,
if DOB is a control on your form as well as a field in the form's
recordset, you might need to rename the control to something else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I tried to apply your suggestion, I got the following error message:
"The expression you entered has a function containing the wrong number of
arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))])



Can you help me to properly rephrase this?



Thanks.

Sue

On Tue, 1 May 2007 22:58:11 -0400, Sue wrote:

I have a date field (for birthdays) on my form but don't know the year
of
birth for many of the people I'd like to remember on their birthdays.
For
them, I put in the year 1900. But for SOME of my contacts, I'd like to
know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to
see the
age "107". So is there a way to ONLY have the age display if the year
is >
1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[Sue]

Can you guys tell me a bit more about "Me" keyword?

Doug, will the Me keyword work outside of a VBA module? i don't think i've
ever seen that used before in an expression in a calculated control. tia,
tina

Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd
")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As well,
you don't care about time, so it's better to use Date() than Now(). Also, if
DOB is a control on your form as well as a field in the form's recordset,
you might need to rename the control to something else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date
(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I tried to apply your suggestion, I got the following error message:
"The expression you entered has a function containing the wrong number of
arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
])

Can you help me to properly rephrase this?



Thanks.

Sue



On Tue, 1 May 2007 22:58:11 -0400, Sue wrote:

I have a date field (for birthdays) on my form but don't know the
year
of
birth for many of the people I'd like to remember on their birthdays.
For
them, I put in the year 1900. But for SOME of my contacts, I'd like
to
know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to see
the
age "107". So is there a way to ONLY have the age display if the year is

1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[Sue]

OK - so now I'm ALL bolllixed up.
I've got a single field, DOB, which contains mm/dd/yyyy for all persons
whose month & day of birth I know. For those whose year of birth I know, I
insert the actual year. For those whose year of birth I DON'T know, I insert
1900.
The precise same rules apply for a second field in which I insert the
anniversary date - DOA.
The union query mentioned below was developed in order to combine DOB & DOA
into a single report in which I can list the events for all persons I want
to send cards to.

Now that I've begun trying to use some of the suggestions offered here, each
time I try to reinsert the original formula I'd had (the one that gave ages
as 107 for those whose year of birth is not known), I get either missing
operand error messages or ?NAME in the field when I look @ my form.

Help?

OH WAIT -
I bet I know the problem.

I'm using a union query to combine the anniversary & birthday dates -
here's the union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;


Now what should I do?

Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As well,
you don't care about time, so it's better to use Date() than Now(). Also,
if DOB is a control on your form as well as a field in the form's
recordset, you might need to rename the control to something else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I tried to apply your suggestion, I got the following error
message: "The expression you entered has a function containing the wrong
number of arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))])



Can you help me to properly rephrase this?



Thanks.

Sue



On Tue, 1 May 2007 22:58:11 -0400, Sue wrote:

I have a date field (for birthdays) on my form but don't know the year
of
birth for many of the people I'd like to remember on their birthdays.
For
them, I put in the year 1900. But for SOME of my contacts, I'd like to
know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to
see the
age "107". So is there a way to ONLY have the age display if the year
is >
1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[Douglas J. Steele]

If DOB is a date field (as opposed to a text field), populating it with 1900
actually is setting it to 14 March, 1905. Date fields must be complete
dates. Under the covers, a date field is an 8 byte floating point number,
where the integer portion represents the date as the number of days relative
to 30 Dec, 1899, and the decimal portion represents the time as a fraction
of a day. 14 March, 1905 happens to be 1900 days after 30 Dec, 1899.

Because you've aliased the field in your union query as CombineDate, that's
how you have to refer to the field: as far as the query is concerned, it
doesn't have a field named DOB (nor one named DOA)

Try:

=IIf([CombinedDate]>1900,Null,DateDiff("yyyy",[CombinedDate],Date())+Int(Format(Date(),"mmdd")<Format([CombinedDate],"mmdd")))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK - so now I'm ALL bolllixed up.
I've got a single field, DOB, which contains mm/dd/yyyy for all persons
whose month & day of birth I know. For those whose year of birth I know, I
insert the actual year. For those whose year of birth I DON'T know, I
insert 1900.
The precise same rules apply for a second field in which I insert the
anniversary date - DOA.
The union query mentioned below was developed in order to combine DOB &
DOA into a single report in which I can list the events for all persons I
want to send cards to.

Now that I've begun trying to use some of the suggestions offered here,
each time I try to reinsert the original formula I'd had (the one that
gave ages as 107 for those whose year of birth is not known), I get either
missing operand error messages or ?NAME in the field when I look @ my
form.

Help?

OH WAIT -
I bet I know the problem.

I'm using a union query to combine the anniversary & birthday dates -
here's the union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;


Now what should I do?

Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As
well, you don't care about time, so it's better to use Date() than
Now(). Also, if DOB is a control on your form as well as a field in the
form's recordset, you might need to rename the control to something
else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I tried to apply your suggestion, I got the following error
message: "The expression you entered has a function containing the
wrong number of arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))])



Can you help me to properly rephrase this?



Thanks.

Sue



On Tue, 1 May 2007 22:58:11 -0400, Sue wrote:

I have a date field (for birthdays) on my form but don't know the
year of
birth for many of the people I'd like to remember on their birthdays.
For
them, I put in the year 1900. But for SOME of my contacts, I'd like
to know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to
see the
age "107". So is there a way to ONLY have the age display if the year
is >
1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[Douglas J. Steele]

"Me" is how you refer to the current instance of a form, report or class.

In other words, if you're working in the class associated with form MyForm,
you can refer to the form as Forms("MyForm") or as Me.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can you guys tell me a bit more about "Me" keyword?

Doug, will the Me keyword work outside of a VBA module? i don't think
i've
ever seen that used before in an expression in a calculated control. tia,
tina

Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd
")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As
well,
you don't care about time, so it's better to use Date() than Now().
Also, if
DOB is a control on your form as well as a field in the form's
recordset,
you might need to rename the control to something else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date
(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I tried to apply your suggestion, I got the following error message:
"The expression you entered has a function containing the wrong number of
arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
])



Can you help me to properly rephrase this?



Thanks.

Sue



On Tue, 1 May 2007 22:58:11 -0400, Sue wrote:

I have a date field (for birthdays) on my form but don't know the
year
of
birth for many of the people I'd like to remember on their
birthdays.
For
them, I put in the year 1900. But for SOME of my contacts, I'd like
to
know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to see
the
age "107". So is there a way to ONLY have the age display if the
year is

1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[Sue]

I populate the date fields as (for example) 01/02/1900 if I don't know the
year of birth. Is that OK?
I tried your expression & still get "Name" in that field when I view the
form.
Sorry to be a pest, and I AM grateful for your help.

If DOB is a date field (as opposed to a text field), populating it with
1900 actually is setting it to 14 March, 1905. Date fields must be
complete dates. Under the covers, a date field is an 8 byte floating point
number, where the integer portion represents the date as the number of
days relative to 30 Dec, 1899, and the decimal portion represents the time
as a fraction of a day. 14 March, 1905 happens to be 1900 days after 30
Dec, 1899.

Because you've aliased the field in your union query as CombineDate,
that's how you have to refer to the field: as far as the query is
concerned, it doesn't have a field named DOB (nor one named DOA)

Try:

=IIf([CombinedDate]>1900,Null,DateDiff("yyyy",[CombinedDate],Date())+Int(Format(Date(),"mmdd")<Format([CombinedDate],"mmdd")))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK - so now I'm ALL bolllixed up.
I've got a single field, DOB, which contains mm/dd/yyyy for all persons
whose month & day of birth I know. For those whose year of birth I know,
I insert the actual year. For those whose year of birth I DON'T know, I
insert 1900.
The precise same rules apply for a second field in which I insert the
anniversary date - DOA.
The union query mentioned below was developed in order to combine DOB &
DOA into a single report in which I can list the events for all persons I
want to send cards to.

Now that I've begun trying to use some of the suggestions offered here,
each time I try to reinsert the original formula I'd had (the one that
gave ages as 107 for those whose year of birth is not known), I get
either missing operand error messages or ?NAME in the field when I look @
my form.

Help?

OH WAIT -
I bet I know the problem.

I'm using a union query to combine the anniversary & birthday dates -
here's the union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;


Now what should I do?



Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As
well, you don't care about time, so it's better to use Date() than
Now(). Also, if DOB is a control on your form as well as a field in the
form's recordset, you might need to rename the control to something
else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I tried to apply your suggestion, I got the following error
message: "The expression you entered has a function containing the
wrong number of arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))])



Can you help me to properly rephrase this?



Thanks.

Sue



On Tue, 1 May 2007 22:58:11 -0400, Sue wrote:

I have a date field (for birthdays) on my form but don't know the
year of
birth for many of the people I'd like to remember on their
birthdays. For
them, I put in the year 1900. But for SOME of my contacts, I'd like
to know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want to
see the
age "107". So is there a way to ONLY have the age display if the
year is >
1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[Douglas J. Steele]

Try calculating the Age/Anniversary in your query, and then binding to the
computed Years field.

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate,
IIf(Year([DOB])>1900,Null,DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
AS Years
FROM tblContacts
UNION ALL
SELECT LastName,FirstName, "Anniversary" As CardType, DOA As CombinedDate
IIf(Year([DOA])>1900,Null,DateDiff("yyyy",[DOA],Date())+Int(Format(Date(),"mmdd")<Format([DOA],"mmdd")))
AS Years
FROM tblContacts
ORDER BY CombinedDate;

Is there a reason you're not storing Null when you don't know the date?
That's what Null is for...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I populate the date fields as (for example) 01/02/1900 if I don't know the
year of birth. Is that OK?
I tried your expression & still get "Name" in that field when I view the
form.
Sorry to be a pest, and I AM grateful for your help.

If DOB is a date field (as opposed to a text field), populating it with
1900 actually is setting it to 14 March, 1905. Date fields must be
complete dates. Under the covers, a date field is an 8 byte floating
point number, where the integer portion represents the date as the number
of days relative to 30 Dec, 1899, and the decimal portion represents the
time as a fraction of a day. 14 March, 1905 happens to be 1900 days after
30 Dec, 1899.

Because you've aliased the field in your union query as CombineDate,
that's how you have to refer to the field: as far as the query is
concerned, it doesn't have a field named DOB (nor one named DOA)

Try:

=IIf([CombinedDate]>1900,Null,DateDiff("yyyy",[CombinedDate],Date())+Int(Format(Date(),"mmdd")<Format([CombinedDate],"mmdd")))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK - so now I'm ALL bolllixed up.
I've got a single field, DOB, which contains mm/dd/yyyy for all persons
whose month & day of birth I know. For those whose year of birth I know,
I insert the actual year. For those whose year of birth I DON'T know, I
insert 1900.
The precise same rules apply for a second field in which I insert the
anniversary date - DOA.
The union query mentioned below was developed in order to combine DOB &
DOA into a single report in which I can list the events for all persons
I want to send cards to.

Now that I've begun trying to use some of the suggestions offered here,
each time I try to reinsert the original formula I'd had (the one that
gave ages as 107 for those whose year of birth is not known), I get
either missing operand error messages or ?NAME in the field when I look
@ my form.

Help?



OH WAIT -
I bet I know the problem.

I'm using a union query to combine the anniversary & birthday dates -
here's the union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;


Now what should I do?



message Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As
well, you don't care about time, so it's better to use Date() than
Now(). Also, if DOB is a control on your form as well as a field in
the form's recordset, you might need to rename the control to
something else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I tried to apply your suggestion, I got the following error
message: "The expression you entered has a function containing the
wrong number of arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))])



Can you help me to properly rephrase this?



Thanks.

Sue



On Tue, 1 May 2007 22:58:11 -0400, Sue wrote:

I have a date field (for birthdays) on my form but don't know the
year of
birth for many of the people I'd like to remember on their
birthdays. For
them, I put in the year 1900. But for SOME of my contacts, I'd like
to know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want
to see the
age "107". So is there a way to ONLY have the age display if the
year is >
1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

 

[Sue]

Issue solved - once again a demonstration of my ignorance.
I had entered an expression that "split" across 2 lines. When I pasted in
another expression, there was some straggling stuff from an earlier entry
that converted the pasted expression into gibberish.
I appreciate all your patience and assistance.

Try calculating the Age/Anniversary in your query, and then binding to the
computed Years field.

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate,
IIf(Year([DOB])>1900,Null,DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
AS Years
FROM tblContacts
UNION ALL
SELECT LastName,FirstName, "Anniversary" As CardType, DOA As CombinedDate
IIf(Year([DOA])>1900,Null,DateDiff("yyyy",[DOA],Date())+Int(Format(Date(),"mmdd")<Format([DOA],"mmdd")))
AS Years
FROM tblContacts
ORDER BY CombinedDate;

Is there a reason you're not storing Null when you don't know the date?
That's what Null is for...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I populate the date fields as (for example) 01/02/1900 if I don't know the
year of birth. Is that OK?
I tried your expression & still get "Name" in that field when I view the
form.
Sorry to be a pest, and I AM grateful for your help.

If DOB is a date field (as opposed to a text field), populating it with
1900 actually is setting it to 14 March, 1905. Date fields must be
complete dates. Under the covers, a date field is an 8 byte floating
point number, where the integer portion represents the date as the
number of days relative to 30 Dec, 1899, and the decimal portion
represents the time as a fraction of a day. 14 March, 1905 happens to be
1900 days after 30 Dec, 1899.

Because you've aliased the field in your union query as CombineDate,
that's how you have to refer to the field: as far as the query is
concerned, it doesn't have a field named DOB (nor one named DOA)

Try:

=IIf([CombinedDate]>1900,Null,DateDiff("yyyy",[CombinedDate],Date())+Int(Format(Date(),"mmdd")<Format([CombinedDate],"mmdd")))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OK - so now I'm ALL bolllixed up.
I've got a single field, DOB, which contains mm/dd/yyyy for all persons
whose month & day of birth I know. For those whose year of birth I
know, I insert the actual year. For those whose year of birth I DON'T
know, I insert 1900.
The precise same rules apply for a second field in which I insert the
anniversary date - DOA.
The union query mentioned below was developed in order to combine DOB &
DOA into a single report in which I can list the events for all persons
I want to send cards to.

Now that I've begun trying to use some of the suggestions offered here,
each time I try to reinsert the original formula I'd had (the one that
gave ages as 107 for those whose year of birth is not known), I get
either missing operand error messages or ?NAME in the field when I look
@ my form.

Help?



OH WAIT -
I bet I know the problem.

I'm using a union query to combine the anniversary & birthday dates -
here's the union query:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As
CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA
As CombinedDate
FROM tblContacts
ORDER BY CombinedDate;


Now what should I do?



message Remove the second equal sign:

=IIf(Year([DOB])=1900,"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")))

Note that you should put Me.[DOB] to help Access find the field. As
well, you don't care about time, so it's better to use Date() than
Now(). Also, if DOB is a control on your form as well as a field in
the form's recordset, you might need to rename the control to
something else.

=IIf(Year(Me.[DOB])=1900,"",DateDiff("yyyy",Me.[DOB],Date())+Int(Format(Date(),"mmdd")<Format(Me.[DOB],"mmdd")))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I tried to apply your suggestion, I got the following error
message: "The expression you entered has a function containing the
wrong number of arguments."

My date form is called DOB
The calculation I'm using in the unbound control is:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))



What I attempted to enter is the following:

=IIf(Year([DOB])=1900,"",[
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))])



Can you help me to properly rephrase this?



Thanks.

Sue



On Tue, 1 May 2007 22:58:11 -0400, Sue wrote:

I have a date field (for birthdays) on my form but don't know the
year of
birth for many of the people I'd like to remember on their
birthdays. For
them, I put in the year 1900. But for SOME of my contacts, I'd
like to know
their age (i.e. for "special" birthdays, like sweet 16 or 21 or
whatever). I
have a second field which calculates age. But I really don't want
to see the
age "107". So is there a way to ONLY have the age display if the
year is >
1900?

Thanks.

Add an unbound control to your form.
=IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])