ACCESS FEED: RE: [MS_AccessPros] Mistake in expression

Sabtu, 08 Oktober 2011

RE: [MS_AccessPros] Mistake in expression - a complication

John,

You are right but I had not foreseen a member is still active and
joined when the club had it's former name.

I have a field [DateTerminated] so I made this query:

SELECT tMembersClubHistory.MemberID, tMembersClubHistory.ClubID,
tMembersClubHistory.DateJoinedThisClub, IIf([DateTerminated] Is
Null,[ClubName],IIf([DateJoinedThisClub]<NZ([ChangeDate],#1/1/1900#),[Formerly],[ClubName]))
AS Club
FROM tClubs INNER JOIN tMembersClubHistory ON tClubs.ClubID =
tMembersClubHistory.ClubID
ORDER BY tMembersClubHistory.DateJoinedThisClub;

Which works in Access but the NZ function is not accepted by the ASP
page on the server.

I am off to bed now. I will look at the problem tomorrow.

Cheers,

Robin

At 8/10/2011 11:22 PM, you wrote:
>Robin-
>
>The original SQL should work. DateJoined is less than ChangedDate,
>so it should
>display the [Formerly] field - that's what I thought you wanted to
>do. What do
>you really want?
>
>John Viescas, author
>Microsoft Office Access 2010 Inside Out
>Microsoft Office Access 2007 Inside Out
>Building Microsoft Access Applications
>Microsoft Office Access 2003 Inside Out
>SQL Queries for Mere Mortals
>http://www.viescas.com/
>(Paris, France)
>
>
>
>-----Original Message-----
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Saturday, October 08, 2011 2:03 PM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: 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],[Clu
>bName],[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
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>