ACCESS FEED: RE: [MS_AccessPros] Mistake in expression

Sabtu, 08 Oktober 2011

RE: [MS_AccessPros] Mistake in expression - a complication

Thanks John,

That works a treat.

Robin Chapple

At 8/10/2011 11:49 PM, you wrote:
>Robin-
>
>If IIf works, then do this:
>
>SELECT tMembersClubHistory.MemberID, tMembersClubHistory.ClubID,
>tMembersClubHistory.DateJoinedThisClub, IIf([DateTerminated] Is
>Null,[ClubName],IIf([DateJoinedThisClub]<IIf([ChangeDate] Is Null, #1/1/1900#,
>[ChangeDate]),[Formerly],[ClubName]))
>AS Club
>FROM tClubs INNER JOIN tMembersClubHistory ON tClubs.ClubID =
>tMembersClubHistory.ClubID
>ORDER BY tMembersClubHistory.DateJoinedThisClub;
>
>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:39 PM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: 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]>[Cha
> ngeDate],[Cl
>u
> >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
> >
> >
> >
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>