ACCESS FEED: RE: [MS_AccessPros] Expressing "This Year" - " Year" or "Years" from a DateDiff expression.

Sabtu, 16 Maret 2013

RE: [MS_AccessPros] Expressing "This Year" - " Year" or "Years" from a DateDiff expression.

Robin-

First, your calculation isn't very accurate. For example,

DateDiff("yyyy", #12/31/2011#, #1/1/2012#) = 1 !!!

All DateDiff is doing is a subtraction on the year parts without regard for
month and day.

The correct way to do it is:

DateDiff("yyyy", qryMemberClubHistoryActive.DateJoinedThisClub, Now())+Int(
Format(now(), "mmdd") <
Format(qryMemberClubHistoryActive.DateJoinedThisClub, "mmdd") )

Now let's plug that into your expression:

IIf(

(DateDiff("yyyy", qryMemberClubHistoryActive.DateJoinedThisClub, Now()) +

Int( Format(now(), "mmdd") <
Format(qryMemberClubHistoryActive.DateJoinedThisClub, "mmdd") )) < 1,
"Joined this year",

IIf( (DateDiff("yyyy", qryMemberClubHistoryActive.DateJoinedThisClub, Now())
+

Int( Format(now(), "mmdd") <
Format(qryMemberClubHistoryActive.DateJoinedThisClub, "mmdd") )) = 1,

(DateDiff("yyyy", qryMemberClubHistoryActive.DateJoinedThisClub, Now()) +

Int( Format(now(), "mmdd") <
Format(qryMemberClubHistoryActive.DateJoinedThisClub, "mmdd") )) & " year",

(DateDiff("yyyy", qryMemberClubHistoryActive.DateJoinedThisClub, Now()) +

Int( Format(now(), "mmdd") <
Format(qryMemberClubHistoryActive.DateJoinedThisClub, "mmdd") )) & "
years"))

I'm pretty sure the parens are correct - tested in the Immediate Window.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Saturday, March 16, 2013 4:29 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Expressing "This Year" - " Year" or "Years" from a
DateDiff expression.

I currently use this SQL in a query to calculate how many years a
member has served.

IIF(DateDiff("yyyy", qryMemberClubHistoryActive.DateJoinedThisClub,
Now()) < 1, "Joined this year", DateDiff("yyyy",
qryMemberClubHistoryActive.DateJoinedThisClub, Now()) & " years")

Of course in the case of '1 year' it is shown as "1 years".

I attempted to separate the single year and failed:

IIF(DateDiff("yyyy", qryMemberClubHistoryActive.DateJoinedThisClub,
Now()) < 1, "Joined this year", (IIF(DateDiff("yyyy",
qryMemberClubHistoryActive.DateJoinedThisClub, Now()) =
1,DateDiff("yyyy", qryMemberClubHistoryActive.DateJoinedThisClub,
Now()) & " year", qryMemberClubHistoryActive.DateJoinedThisClub,
Now()) & " years")

What have I missed?

Many thanks,

Robin Chapple

[Non-text portions of this message have been removed]