sql syntax

S

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!
 
D

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.
 
D

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...
 
S

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vb sql modification 2
sql 7
invalid procedure call or argument 2
Have to delete record twice 3
selecting union query results 7
Summing a sum 16
Union query- count records 2
Union Query SQL 2

Top