Prev: open form/subform at specific record
Next: How do I get rid of "getting started with Microsoft Office Access"
From: KenSheridan via AccessMonster.com on 12 Feb 2010 16:23
Laura:

Sorry, it should be a minus sign throughout. Put it down to how depressingly
high the result is when I apply the expression to own date of birth!

The way it works is that the DateDiff function gets the straight difference
in months between the two dates regardless of the day of the month in eaither,
so if the day of the month of the date of birth is after the day of the month
of the current date one month has to be subtracted so that the result is
whole months only. To see the difference enter the following in the debug
window:

? DateDiff("m",#2000-10-11#,#2010-02-12#)-IIf(Day(#2000-11-12#)>Day(#2010-02-
12#),1,0)

and then enter:

? DateDiff("m",#2000-10-13#,#2010-02-12#)-IIf(Day(#2000-10-13#)>Day(#2010-02-
12#),1,0)

Now where did I leave that Zimmer frame?

Ken Sheridan
Stafford, England

Laura wrote:
>Ken, thanks for replying so quickly, it's so helpful.
>Your formula works - thank you so much. I got slightly confused - did you
>mean a minus or plus sign before the IIF? I'm not sure of the significance.
>
>_________________________________________________
>DateDiff("m",[DoB],Date())+IIf(Day([DoB])>Day(Date()),1,0)
>AvgAgeInMonths:
>AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)>Day(Date()),1,0))
>_________________________________________________
>
>Either seems to work. I used it in the Query Builder window and can program
>it to "Enter Date" so that the school can use it for each of the 3 terms of
>the year to then mailmerge into the School Reports.
>
>Many thanks again.
>Laura
>Wimbledon
>London
>UK
>
>"KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote
>Correction. First expression should have been:
>DateDiff("m",[DoB],Date())+IIf(Day([DoB])>Day(Date()),1,0)
>
>> Laura:
>>
>[quoted text clipped - 56 lines]
>>>Thanks
>>>Laura

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201002/1

From: Laura on 13 Feb 2010 14:52
Thanks for explaining, Ken - I know the feeling when applying to own
birthdate :(

I think the same principle applies if you're working out the age of a person
in terms of years.. you have to subtract a year, or something, or it works
out that you're a year older than you are and that's even worse!

Laura
London


"KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote in message
news:a38a3dca882ac(a)uwe...
> Laura:
>
> Sorry, it should be a minus sign throughout. Put it down to how
> depressingly
> high the result is when I apply the expression to own date of birth!
>
> The way it works is that the DateDiff function gets the straight
> difference
> in months between the two dates regardless of the day of the month in
> eaither,
> so if the day of the month of the date of birth is after the day of the
> month
> of the current date one month has to be subtracted so that the result is
> whole months only. To see the difference enter the following in the debug
> window:
>
> ?
> DateDiff("m",#2000-10-11#,#2010-02-12#)-IIf(Day(#2000-11-12#)>Day(#2010-02-
> 12#),1,0)
>
> and then enter:
>
> ?
> DateDiff("m",#2000-10-13#,#2010-02-12#)-IIf(Day(#2000-10-13#)>Day(#2010-02-
> 12#),1,0)
>
> Now where did I leave that Zimmer frame?
>
> Ken Sheridan
> Stafford, England
>
> Laura wrote:
>>Ken, thanks for replying so quickly, it's so helpful.
>>Your formula works - thank you so much. I got slightly confused - did you
>>mean a minus or plus sign before the IIF? I'm not sure of the
>>significance.
>>
>>_________________________________________________
>>DateDiff("m",[DoB],Date())+IIf(Day([DoB])>Day(Date()),1,0)
>>AvgAgeInMonths:
>>AVG(DateDiff("m",[DoB],Date())-IIf(Day(DoB)>Day(Date()),1,0))
>>_________________________________________________
>>
>>Either seems to work. I used it in the Query Builder window and can
>>program
>>it to "Enter Date" so that the school can use it for each of the 3 terms
>>of
>>the year to then mailmerge into the School Reports.
>>
>>Many thanks again.
>>Laura
>>Wimbledon
>>London
>>UK
>>
>>"KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote
>>Correction. First expression should have been:
>>DateDiff("m",[DoB],Date())+IIf(Day([DoB])>Day(Date()),1,0)
>>
>>> Laura:
>>>
>>[quoted text clipped - 56 lines]
>>>>Thanks
>>>>Laura
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201002/1
>


From: KenSheridan via AccessMonster.com on 14 Feb 2010 13:26
Laura:

One other thing worth mentioning is that when you have a parameter in a query
to enter a date time value it's a good idea to declare the parameter in the
query. This then avoids any possibility of a date being entered in short
date format, e.g. 14/2/2010, being misinterpreted as an arithmetical
expression. If this does happen it wouldn't raise an error as Access
implements the date/time data type as a 64 bit floating point number, the
integer part representing the days and the fractional part the times of day,
so it would be interpreted as the date time value which the number represents.
14/2/2010 as an arithmetical expression results in a number which represents
a date time value of 30 December 1899 00:05:01. This is because 30 December
1899 is 'day zero' in Access. You can see this by entering the following in
the debug window:

? Format(CDate(14/2/2010),"dd mmmm yyyy hh:nn:ss")

Parameters can be declared in query design view by selecting Parameters from
the Query menu (or whatever the equivalent is in Access 2007), or in SQL view
by adding a line to the beginning of the query. So for a parameter [Enter
Date] it would be:

PARAMETERS [Enter Date] DateTime;
SELECT etc

You can then be assured that however the user enters the date, provided it is
a legitimate date value, it will always be interpreted correctly.

Ken Sheridan
Stafford, England

Laura wrote:
>Thanks for explaining, Ken - I know the feeling when applying to own
>birthdate :(
>
>I think the same principle applies if you're working out the age of a person
>in terms of years.. you have to subtract a year, or something, or it works
>out that you're a year older than you are and that's even worse!
>
>Laura
>London
>
>> Laura:
>>
>[quoted text clipped - 59 lines]
>>>>>Thanks
>>>>>Laura

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201002/1

From: Laura on 15 Feb 2010 16:24
Ken, you're a star - thank you so much.=20

I find that nearly every single time I need to use "date" it's different =
from the previous time and therefore the coding is different too. I =
think one could write a whole book just on "dates". I keep saving =
valuable notes such as yours and appreciate your added input. Thank you =
very much. :)

Laura
London


"KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote in message
news:a3a1d70f521c6(a)uwe...
> Laura:
>
> One other thing worth mentioning is that when you have a parameter in a
> query
> to enter a date time value it's a good idea to declare the parameter in
> the
> query. This then avoids any possibility of a date being entered in short
> date format, e.g. 14/2/2010, being misinterpreted as an arithmetical
> expression. If this does happen it wouldn't raise an error as Access
> implements the date/time data type as a 64 bit floating point number, the
> integer part representing the days and the fractional part the times of
> day,
> so it would be interpreted as the date time value which the number
> represents.
> 14/2/2010 as an arithmetical expression results in a number which
> represents
> a date time value of 30 December 1899 00:05:01. This is because 30
> December
> 1899 is 'day zero' in Access. You can see this by entering the following
> in
> the debug window:
>
> ? Format(CDate(14/2/2010),"dd mmmm yyyy hh:nn:ss")
>
> Parameters can be declared in query design view by selecting Parameters
> from
> the Query menu (or whatever the equivalent is in Access 2007), or in SQL
> view
> by adding a line to the beginning of the query. So for a parameter [Enter
> Date] it would be:
>
> PARAMETERS [Enter Date] DateTime;
> SELECT etc
>
> You can then be assured that however the user enters the date, provided it
> is
> a legitimate date value, it will always be interpreted correctly.
>
> Ken Sheridan
> Stafford, England
>
> Laura wrote:
>>Thanks for explaining, Ken - I know the feeling when applying to own
>>birthdate :(
>>
>>I think the same principle applies if you're working out the age of a
>>person
>>in terms of years.. you have to subtract a year, or something, or it works
>>out that you're a year older than you are and that's even worse!
>>
>>Laura
>>London
>>
>>> Laura:
>>>
>>[quoted text clipped - 59 lines]
>>>>>>Thanks
>>>>>>Laura
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201002/1
>


First  |  Prev  | 
Pages: 1 2
Prev: open form/subform at specific record
Next: How do I get rid of "getting started with Microsoft Office Access"