sql

[Stephanie]

Hi. I'm using this sql in my code and it works fine
(thanks to the fabulous list-savers!). But I need to add
another factor and can't get the sytax correct.

I don't want Contacts.EmailName to be null AND I don't
want ChapterMembers.ChapterMemberID to be null. How can I
add that to the sql statement? Thanks, Stephanie

sql = "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);"

 

[Ken Snell [MVP]]

Try this:

sql = "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 AND ChapterMembers.ChapterMemberID Is Not Null;"

 

[John Vinson]

I don't want Contacts.EmailName to be null AND I don't
want ChapterMembers.ChapterMemberID to be null. How can I
add that to the sql statement? Thanks, Stephanie

If ChapterMemberID is the Primary Key of ChapterMembers then it cannot
be Null in any case so no test should be necessary. But if it is...
try

sql = "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) AND [ChapterMembers].[ChapterMemberID] IS NOT NULL"

John W. Vinson[MVP]

 

[Stephanie]

Yes, I have come to the conclusion that the issue is not
that ChapterMember is null. (but thanks for help with
syntax- that will come in handy!)

The issue is that I am using a parameter form to give
Me.FindChapter for "WHERE (((ChapterMembers.ChapterID)=" +
CStr(Me.FindChapter) + ")

It works if I have a ChapterMember in the
correct "FindChapter" (meaning that when I request info
about chapter members, there are chapter members in the
given chapter) but if I don't have a ChapterMember in a
chapter, I get an error message: run-time error 5 invalid
procedure call or argument. And debug sends me to my line:
strTo = Left(strTo, Len(strTo) - 1)

with strTo = "" (meaning that there is no email address
to send it to). So, I'm trying to skip chapters where
there are no members. ChapterMembers has 3 fields:
ChapterMemberID, ContactID and ChapterID.

Any suggestions?

-----Original Message-----

I don't want Contacts.EmailName to be null AND I don't
want ChapterMembers.ChapterMemberID to be null. How can I
add that to the sql statement? Thanks, Stephanie

If ChapterMemberID is the Primary Key of ChapterMembers then it cannot
be Null in any case so no test should be necessary. But if it is...
try

sql = "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) AND [ChapterMembers].[ChapterMemberID] IS NOT NULL"

John W. Vinson[MVP]
.

 

[G. Vaught]

If you can create a query against your chapters and perform a count against
the number of members for each chapter and filter for counts >0, then you
can use that query as a subquery in your current query to limit the list.

Did that make sense.

Then you can combine this query

Yes, I have come to the conclusion that the issue is not
that ChapterMember is null. (but thanks for help with
syntax- that will come in handy!)

The issue is that I am using a parameter form to give
Me.FindChapter for "WHERE (((ChapterMembers.ChapterID)=" +
CStr(Me.FindChapter) + ")

It works if I have a ChapterMember in the
correct "FindChapter" (meaning that when I request info
about chapter members, there are chapter members in the
given chapter) but if I don't have a ChapterMember in a
chapter, I get an error message: run-time error 5 invalid
procedure call or argument. And debug sends me to my line:
strTo = Left(strTo, Len(strTo) - 1)

with strTo = "" (meaning that there is no email address
to send it to). So, I'm trying to skip chapters where
there are no members. ChapterMembers has 3 fields:
ChapterMemberID, ContactID and ChapterID.

Any suggestions?

-----Original Message-----

I don't want Contacts.EmailName to be null AND I don't
want ChapterMembers.ChapterMemberID to be null. How can I
add that to the sql statement? Thanks, Stephanie

If ChapterMemberID is the Primary Key of ChapterMembers then it cannot
be Null in any case so no test should be necessary. But if it is...
try

sql = "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) AND [ChapterMembers].[ChapterMemberID] IS NOT NULL"

John W. Vinson[MVP]
.

 

[Stephanie]

Thanks- that does make sense. Here's the ChapterCount
subquery:
SELECT Count(ChapterMembers.ChapterMemberID) AS
CountOfChapterMemberID, Chapter.ChapterID
FROM Chapter INNER JOIN ChapterMembers ON
Chapter.ChapterID = ChapterMembers.ChapterID
GROUP BY Chapter.ChapterID
HAVING (((Count(ChapterMembers.ChapterMemberID))>0));

Giving that my vb sql skills are terrible, how do I
incorporate the subquery into the query:

sql = "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));"

Thanks,
Stephanie

-----Original Message-----
If you can create a query against your chapters and perform a count against
the number of members for each chapter and filter for counts >0, then you
can use that query as a subquery in your current query to limit the list.

Did that make sense.

Then you can combine this query

Yes, I have come to the conclusion that the issue is not
that ChapterMember is null. (but thanks for help with
syntax- that will come in handy!)

The issue is that I am using a parameter form to give
Me.FindChapter for "WHERE (((ChapterMembers.ChapterID) =" +
CStr(Me.FindChapter) + ")

It works if I have a ChapterMember in the
correct "FindChapter" (meaning that when I request info
about chapter members, there are chapter members in the
given chapter) but if I don't have a ChapterMember in a
chapter, I get an error message: run-time error 5 invalid
procedure call or argument. And debug sends me to my line:
strTo = Left(strTo, Len(strTo) - 1)

with strTo = "" (meaning that there is no email address
to send it to). So, I'm trying to skip chapters where
there are no members. ChapterMembers has 3 fields:
ChapterMemberID, ContactID and ChapterID.

Any suggestions?

-----Original Message-----
On Sat, 26 Feb 2005 14:14:12 -0800, "Stephanie"

I don't want Contacts.EmailName to be null AND I don't
want ChapterMembers.ChapterMemberID to be null. How

can
I

add that to the sql statement? Thanks, Stephanie

If ChapterMemberID is the Primary Key of ChapterMembers then it cannot
be Null in any case so no test should be necessary. But if it is...
try

sql = "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) AND [ChapterMembers].[ChapterMemberID] IS NOT NULL"

John W. Vinson[MVP]
.

.

 

[Stephanie]

Thanks. It's interesting that there are so many methods
to write the code, and I'm bad at all of them!

 

[John Vinson]

Hi. I'm using this sql in my code and it works fine
(thanks to the fabulous list-savers!). But I need to add
another factor and can't get the sytax correct.

I don't want Contacts.EmailName to be null AND I don't
want ChapterMembers.ChapterMemberID to be null. How can I
add that to the sql statement? Thanks, Stephanie

sql = "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);"

Unless I'm missing something, the query as written does exactly what
you ask. Even if Me.FindChapter is empty, it will give an error rather
than retrieving NULL records. What is in the FindChapter control? Can
you show some records which violate your desired criterion?

John W. Vinson[MVP]