Prev: new navigation control Access 2010beta
Next: Textbox Character Counter
From: Biss on 19 Jan 2010 23:55


Hi John,

Many thanks but this calculates the age in correctly
=DateDiff("yyyy", [DOB], Date())

If I have DOB of 07-Jun-85
Then the age should be 24 but the result of 25 is returned.

When I add this
- IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)

I receive an error that says that I am missing a [ or |

Thanks

Bob

>
> To calculate the age, set the Control Source of a textbox on the form to
>
> =DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
> Format(Date(), "mmdd", 1, 0)

>>
> all on one line.
> --
>
> John W. Vinson [MVP]
>>I have tried this in various places as well as event procedures and have
>>had
>>no results.
>>
>>=[DOBDay] & "-" & [DOBMn] & "-" & [DOBYr]
>>
>>Once I have that data I would like to have text box to display the age..
>>The
>>age does not need to be stored in the table..
>>
>>I know that this should be simple but just cant get my hear around it.
>>
>>Thanks in advance
>>
>>Bob
>>
From: John W. Vinson on 20 Jan 2010 12:59
On Tue, 19 Jan 2010 23:55:41 -0500, "Biss" <raleygroupky(a)hotmail.com> wrote:

>
>
>Hi John,
>
>Many thanks but this calculates the age in correctly
>=DateDiff("yyyy", [DOB], Date())
>
>If I have DOB of 07-Jun-85
>Then the age should be 24 but the result of 25 is returned.
>
>When I add this
>- IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)
>
>I receive an error that says that I am missing a [ or |


Sorry, typo on my part. The entire expression - all on one line - should be

=DateDiff("yyyy", [DOB], Date()) -
IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd"), 1, 0)

The DateDiff function itself returns the number of year changes between the
dates - so a baby born December 30 2009 would be shown as a year old on
January 2 2010; the IIF statement (if you put all the parentheses in it, which
I didn't) will subtract 1 if the birthday anniversary has not yet been reached
this year.
--

John W. Vinson [MVP]
From: Biss on 20 Jan 2010 15:48
John,, I am sorry

But I still get the same error.

Old expression
> =DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd"), 1, 0)

New Expressoin
> =DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)

Same error missin [ or |

Sorry for the bother but I can seem to figure our what is missing.. If I use builder I come up with same as you do.. A real head scratcher.

sorry but thanks for the help

Bob




"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:lugel59bqatm8b5irlqkljho2vspk1lt6q(a)4ax.com...
> On Tue, 19 Jan 2010 23:55:41 -0500, "Biss" <raleygroupky(a)hotmail.com> wrote:
>
>>
>>
>>Hi John,
>>
>>Many thanks but this calculates the age in correctly
>>=DateDiff("yyyy", [DOB], Date())
>>
>>If I have DOB of 07-Jun-85
>>Then the age should be 24 but the result of 25 is returned.
>>
>>When I add this
>>- IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)
>>
>>I receive an error that says that I am missing a [ or |
>
>
> Sorry, typo on my part. The entire expression - all on one line - should be
>
> =DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd"), 1, 0)
> =DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)
>
>
> The DateDiff function itself returns the number of year changes between the
> dates - so a baby born December 30 2009 would be shown as a year old on
> January 2 2010; the IIF statement (if you put all the parentheses in it, which
> I didn't) will subtract 1 if the birthday anniversary has not yet been reached
> this year.
> --
>
> John W. Vinson [MVP]
From: John W. Vinson on 20 Jan 2010 16:48
On Wed, 20 Jan 2010 15:48:39 -0500, "Biss" <raleygroupky(a)hotmail.com> wrote:

>John,, I am sorry
>
>But I still get the same error.
>
>Old expression
>> =DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd"), 1, 0)
>
>New Expressoin
>> =DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)
>
>Same error missin [ or |
>
>Sorry for the bother but I can seem to figure our what is missing.. If I use builder I come up with same as you do.. A real head scratcher.

Hrm. The Old expression looks correct! What is the context? I was assuming
that this was in the Control Source property of a Textbox on a form (or
report). Is it? Or is it someplace else? Do you perhaps have a field or a
control named Date (which would cause conflicts with the Date() function)?

--

John W. Vinson [MVP]
From: Biss on 20 Jan 2010 21:13
John

The old expression works now for some reason while it did not the other day
>>Old expression
>>> =DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
>>> Format(Date(), "mmdd"), 1, 0)

But It stills returns wrong age..

DOB of 07-Jun-85 I get 25 years old.

Yes it is a text box on a form

"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
news:neuel5dnii6o7aeeabq880vthmhv5ori47(a)4ax.com...
> On Wed, 20 Jan 2010 15:48:39 -0500, "Biss" <raleygroupky(a)hotmail.com>
> wrote:
>
>>John,, I am sorry
>>
>>But I still get the same error.
>>
>>Old expression
>>> =DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
>>> Format(Date(), "mmdd"), 1, 0)
>>
>>New Expressoin
>>> =DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
>>> Format(Date(), "mmdd", 1, 0)
>>
>>Same error missin [ or |
>>
>>Sorry for the bother but I can seem to figure our what is missing.. If I
>>use builder I come up with same as you do.. A real head scratcher.
>
> Hrm. The Old expression looks correct! What is the context? I was assuming
> that this was in the Control Source property of a Textbox on a form (or
> report). Is it? Or is it someplace else? Do you perhaps have a field or a
> control named Date (which would cause conflicts with the Date() function)?
>
> --
>
> John W. Vinson [MVP]

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: new navigation control Access 2010beta
Next: Textbox Character Counter