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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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?



"Sue" <nc999sue9990514@xxxxxxxxxx> wrote in message
news:uWowj2MjHHA.4624@xxxxxxxxxxxxxxxxxxxxxxx
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?



"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:%23IQ3D3LjHHA.2368@xxxxxxxxxxxxxxxxxxxxxxx
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
http://I.Am/DougSteele
(no e-mails, please!)


"Sue" <nc999sue9990514@xxxxxxxxxx> wrote in message
news:uTnisILjHHA.3940@xxxxxxxxxxxxxxxxxxxxxxx
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



"fredg" <fgutkind@xxxxxxxxxxxxxxx> wrote in message
news:1cojqnw3b8jdl$.1am7j6tbif5xh.dlg@xxxxxxxxxxxxx
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
Please respond only to this newsgroup.
I do not reply to personal e-mail








.

Relevant Pages

  • Re: Id like NOT to see something on my form.
    ... SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate, ... SELECT LastName,FirstName, "Anniversary" As CardType, DOA As CombinedDate ... Because you've aliased the field in your union query as CombineDate, ... whose month & day of birth I know. ...
    (microsoft.public.access.forms)
  • Re: Id like NOT to see something on my form.
    ... Because you've aliased the field in your union query as CombineDate, ... I've got a single field, DOB, which contains mm/dd/yyyy for all persons ... whose month & day of birth I know. ... Add an unbound control to your form. ...
    (microsoft.public.access.forms)
  • Re: Year of Education
    ... Assuming you mean you subtract the year of birth, not the DOB, that sounds ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.forms)
  • Re: Calculate present age from DOB in form field and system date?
    ... Zane wrote: ... that calculates the present age in years, months, days from the date of birth ... (DOB) ...
    (microsoft.public.word.vba.general)
  • Re: Missing Data
    ... The expression worked....The database I created is for a study that requires ... age not DOB and we are just looking at a single time point not over time. ... I was not aware that HIPPA considered DOB restricted info. ... Date of Birth is an identifier that is sometimes ...
    (microsoft.public.access.queries)