Sabtu, 08 Oktober 2011

RE: [MS_AccessPros] Mistake in expression - a complication

 

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]>[ChangeDate],[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

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar