sql syntax

[Stephanie]

Hello. I've made a minor change to my db and now I can't figure out how to
make a change to sql.

I'm now allowing for 2 email addresses. I'm using EmailFlag which tells me
if I'm to use EmailName1 or EmailName2. Before I just had EmailName.

I was able to fix the "straight" sql:
SELECT IIf([EmailFlag]=1,[contacts].[EmailName1],[contacts].[EmailName2]) AS
EmailName, Chapter.ChapterName
FROM Chapter INNER JOIN (Contacts INNER JOIN ChapterMembers ON
Contacts.ContactID = ChapterMembers.ContactID) ON Chapter.ChapterID =
ChapterMembers.ChapterID
WHERE (((IIf([EmailFlag]=1,[contacts].[EmailName1],[contacts].[EmailName2]))
Is Not Null) AND
((Chapter.ChapterName)=[Forms]![ChapterParamEmail]![FindChapter]));

This works just fine. But, I also have code that uses the same idea as
above. Unfortunately, I can't figure out how to handle
IIf([EmailFlag]=1,[contacts].[EmailName1],[contacts].[EmailName2]) AS
EmailName
embedded in the code.
Here's the "old" code:
"SELECT Contacts.EmailName FROM ChapterMembers INNER JOIN " + "Contacts ON
ChapterMembers.ContactID = Contacts.ContactID " + "WHERE
(((ChapterMembers.ChapterID)=" + CStr(Me.FindChapter) + ") AND
((Contacts.EmailName) Is Not Null));"

I'd appreciate your help. Thanks!

 

[David S via AccessMonster.com]

I'd be inclined to add a query in between your new table and wherever else
you use it, maybe called ContactView, to choose the EmailName:
SELECT Contact.*, IIf([EmailFlag]=1,[contacts].[EmailName1],[contacts].
[EmailName2]) AS EmailName FROM Contact

Then, in all your other SQL statements, you can just replace Contact with
ContactView and they should all work fine.

 

[David S via AccessMonster.com]

Incidentally, this would be a good reason to break out the e-mail addresses
into a separate table altogether, called ContactEmail;
ContactID, EmailNumber, EmailName

That way, you can easily increase the number of Email addresses you want to
store and more readily get to a particular one, like the primary one. Just a
thought...

 

[Stephanie]

David,
As usual, you are the query expert with advice spot-on. Thanks! I didn't
even think of using a query within a vb sql statement.
I'll think about adding a new table...
Thanks for your help!