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

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

 

[Fred Boer]

Dear Doug (and David...):

Sue and I have chatted a bit about her database...

Sue wants to be able to send out birthday and anniversary greetings to a
contact list. On occasion, contacts will provide her the day and month of a
birthday (or anniversary), but won't provide the year of birth/marriage. The
"dummy" year of 1900 is used in these situtations. It allows the use of the
date/time data type (and all those nice date/time functions..

I've seen this suggested as an approach in the newgroups, but I'd be happy
(and I know Sue would be too) to entertain any advice/options for dealing
with this!

Cheers!
Fred Boer