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])