IIf?

[Stephanie]

Hi. I have a sql statement that brings back the volunteer opportunity
information as well as the associated coordinator. If a volunteer
opportunity needs a coordinator, I would expected not to see a name for the
coordinator. However, even if no coordinator is selected for the volunteer
opportunity, the query is returning the name of the first person associated
with the volunteer opportunity as the coordinator. The field I'd like to see
blank is the [Member Name] field. I'd appreciate any suggestions:

SELECT DISTINCT Volunteering.VolunteerName, Volunteering.VolunteerOngoing,
Volunteering.VolunteeringID, Event.Coordinator, Event.CoordinatorNeeded,
Nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name]
FROM ((Volunteering INNER JOIN Event ON Volunteering.VolunteeringID =
Event.VolunteeringID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) INNER JOIN (Organizations INNER JOIN EventSponsors ON
Organizations.OrganizationID = EventSponsors.OrganizationID) ON
Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND ((Event.Coordinator)=Yes))
OR (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.CoordinatorNeeded)=Yes));

 

[David Lloyd]

Stephanie:

I do not have any information regarding the data types of the fields in your
query. However, assuming the "CoordinatorNeeded" field is a True/False
field, you could write an IIF statement for the Member Name field. For
example:

IIF(Event.CoordinatorNeeded=True, "", Nz([NickName],[FirstName]) & " " &
[LastName]) as [Member Name]

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi. I have a sql statement that brings back the volunteer opportunity
information as well as the associated coordinator. If a volunteer
opportunity needs a coordinator, I would expected not to see a name for the
coordinator. However, even if no coordinator is selected for the volunteer
opportunity, the query is returning the name of the first person associated
with the volunteer opportunity as the coordinator. The field I'd like to see
blank is the [Member Name] field. I'd appreciate any suggestions:

SELECT DISTINCT Volunteering.VolunteerName, Volunteering.VolunteerOngoing,
Volunteering.VolunteeringID, Event.Coordinator, Event.CoordinatorNeeded,
Nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name]
FROM ((Volunteering INNER JOIN Event ON Volunteering.VolunteeringID =
Event.VolunteeringID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) INNER JOIN (Organizations INNER JOIN EventSponsors ON
Organizations.OrganizationID = EventSponsors.OrganizationID) ON
Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND ((Event.Coordinator)=Yes))
OR (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.CoordinatorNeeded)=Yes));

 

[Stephanie]

David,
Thanks!! This is excellent and gave me the step I was missing to write a
union query. I appreciate you taking the time to respond!
Cheers!