Robin-
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.
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.
Perhaps what you mean to do is:
Club: IIf([DateJoinedThisClub]<[ChangeDate], [Formerly], [ClubName])
I assume there something in Formerly only if there's something in ChangeDate.
You perhaps also need to guard for ChangeDate being Null:
Club: IIf([DateJoinedThisClub]<NZ([ChangeDate], #1/1/1900#), [Formerly],
[ClubName])
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 7:56 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Mistake in expression
I have a database that lists our clubs. Some have changed their name
over the years. The recorded fields are;
Current name of the club: ClubName
Previous name of the club: Formerly
Date that the name changed: ChangeDate
Date that member joined the club: DateJoinedThisClub
I am attempting to build a history of the members in each club. I
have written this code that is supposed to fill the new field "Club"
with the name of the club at the time the member joined.
Club: IIf([DateJoinedThisClub]<[ChangeDate],[ClubName],[Formerly])
It does show the previous club but does not show the current club.
What have I missed?
Many thanks,
Robin Chapple
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar