ACCESS FEED: RE: [MS_AccessPros] Mistake in expression

Sabtu, 08 Oktober 2011

RE: [MS_AccessPros] Mistake in expression - a complication

I jumped with joy too soon. I have a problem where a current member
joined the club before the name changed. They are shown as members of
the non existent club.

I have tried several permutations and they either give me the new
club name for both entries or the same as the suggested ones below.

Here is one attempt:

Test:
IIf([DateJoinedThisClub]>[ChangeDate],IIf([DateJoinedThisClub]>[ChangeDate],[ClubName],[Formerly]),[ClubName])

Is there a way around this problem?

Many thanks,

Robin Chapple
~~~~~~~~~~~~~~~~~~~~~~~~~~~ Original message ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks John,

Both your expressions worked. I will use the second one.

At 8/10/2011 06:00 PM, you wrote:

>For starters, ClubName and Formerly are in the wrong place! That information
>should be associated with the club, and not each member. If you're pulling
>member info and club info from separate tables, then that would make
>more sense.

Three tables are involved. Members data is recorded in "tMembers" and
the "MemberID" is recorded in the second table "tMembersClubHistory".
The third table is "tClubs" which includes the fields "ClubName" and
"Formerly" and "ChangeDate".

This is the complete SQL:

SELECT tMembersClubHistory.MemberID,
tMembersClubHistory.DateJoinedThisClub,
IIf([DateJoinedThisClub]<[ChangeDate],[Formerly],[ClubName]) AS Club
FROM tClubs INNER JOIN tMembersClubHistory ON tClubs.ClubID =
tMembersClubHistory.ClubID;

>And to answer your specific question, your IIf is backwards. Right
>now, if the
>date joined is before the change date, you're displaying the current ClubName.

OOOPS! I should have known that! Very careless.

Regards,

Robin