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

		Home \| Contact Us \| FAQ \| Search & Site Map \| Link to Us
		Sign In \| Join \| Other 45 Sites in Network

Discussion Groups Forms Forms Programming Queries Modules / DAO / VBA Reports / Printing Macros Database Design Security Conversion Importing / Linking SQL Server / ADP Multiuser / Networking Replication Setup / Configuration Developer Toolkits ActiveX Controls New Users General 1 General 2

Access Directory Tools Tutorials User Groups

Related Topics

SQL Server Other DB Products MS Office More Topics ...

MS Access Forum / Forms / May 2007

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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sue - 02 May 2007 03:58 GMT

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.

Reply to this Message

NevilleT - 02 May 2007 04:26 GMT

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
[quoted text clipped - 5 lines]
>
> Thanks.

Reply to this Message

tina - 02 May 2007 05:17 GMT

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
>
[quoted text clipped - 17 lines]
> >
> > Thanks.

Reply to this Message

Sue - 02 May 2007 13:00 GMT

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.
[quoted text clipped - 48 lines]
>> >
>> > Thanks.

Reply to this Message

tina - 02 May 2007 14:33 GMT

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
[quoted text clipped - 3 lines]
>
> =IIf(Year[DOB])>1900,

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

> Thanks for your help, Tina.
>
[quoted text clipped - 50 lines]
> >> >
> >> > Thanks.

Reply to this Message

Sue - 02 May 2007 16:19 GMT

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:
>
[quoted text clipped - 102 lines]
>> >> >
>> >> > Thanks.

Reply to this Message

Sue - 02 May 2007 16:21 GMT

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:
>
[quoted text clipped - 102 lines]
>> >> >
>> >> > Thanks.

Reply to this Message

fredg - 02 May 2007 05:06 GMT

> 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
[quoted text clipped - 5 lines]
>
> Thanks.

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

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Sue - 02 May 2007 13:05 GMT

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
[quoted text clipped - 12 lines]
> Add an unbound control to your form.
> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Douglas J. Steele - 02 May 2007 14:28 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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
[quoted text clipped - 33 lines]
>> Add an unbound control to your form.
>> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

tina - 02 May 2007 14:42 GMT

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

> > 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?
> >
[quoted text clipped - 20 lines]
> >> Add an unbound control to your form.
> >> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Douglas J. Steele - 02 May 2007 15:51 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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,
[quoted text clipped - 60 lines]
>> >> Add an unbound control to your form.
>> >> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Sue - 02 May 2007 16:22 GMT

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,
[quoted text clipped - 60 lines]
>> >> Add an unbound control to your form.
>> >> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Douglas J. Steele - 02 May 2007 18:20 GMT

"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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Can you guys tell me a bit more about "Me" keyword?
>
[quoted text clipped - 68 lines]
>>> >> Add an unbound control to your form.
>>> >> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Sue - 02 May 2007 16:22 GMT

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:
>
[quoted text clipped - 44 lines]
>>> Add an unbound control to your form.
>>> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Sue - 02 May 2007 16:40 GMT

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.
[quoted text clipped - 59 lines]
>>>> Add an unbound control to your form.
>>>> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Douglas J. Steele - 02 May 2007 18:19 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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
[quoted text clipped - 79 lines]
>>>>> Add an unbound control to your form.
>>>>> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Sue - 02 May 2007 19:03 GMT

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
[quoted text clipped - 95 lines]
>>>>>> Add an unbound control to your form.
>>>>>> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Douglas J. Steele - 02 May 2007 20:04 GMT

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...

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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?
[quoted text clipped - 101 lines]
>>>>>>> Add an unbound control to your form.
>>>>>>> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Sue - 02 May 2007 22:23 GMT

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.
[quoted text clipped - 119 lines]
>>>>>>>> Add an unbound control to your form.
>>>>>>>> =IIf(Year([DateOfBirth])=1900,"",[AgeCalculation])

Reply to this Message

Fred Boer - 02 May 2007 23:26 GMT

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

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

Reply to this Message

David W. Fenton - 02 May 2007 22:14 GMT

> I populate the date fields as (for example) 01/02/1900 if I don't
> know the year of birth. Is that OK?

No. You should just store a Null.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

Sign In

Join

My Latest Posts

My Monitored Threads

My Photo Gallery

My Homepage

Start New Thread

Enable EMail Alerts

Rate this Thread

©2009 Advenet LLC Privacy Policy - Terms of Use

This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.