sql limited by parameter

S

Stephanie

Hi. I am able to loop through my records and create an email, EXCEPT I'm not
limiting my email addresses based on one of the parameters.

I have Contacts that hold Licenses. I want to send an email to all Contacts
(that are in good standing) and have a current license as stated in the input
parameter- this is the part that is not working.

The set up: Contacts hold contact info, Licenses hold license info,
LicenseMembers is the many-to-one linking table.

I have a LiceseQuery that works:
SELECT Contacts.ContactID, Contacts.EmailName1, nz([NickName],[FirstName]) &
" " & [LastName] AS [Member Name], Licenses.LicenseName,
licenseMembers.LicenseID, licenseMembers.LicenseStart,
licenseMembers.LicenseEnd, Contacts.StateOrProvince1,
MemberStatus.MemberStatusID
FROM (Licenses INNER JOIN (Contacts INNER JOIN licenseMembers ON
Contacts.ContactID = licenseMembers.ContactID) ON Licenses.LicenseID =
licenseMembers.LicenseID) INNER JOIN MemberStatus ON Contacts.MemberStatusID
= MemberStatus.MemberStatusID
WHERE (((Licenses.LicenseName)=[forms]![LicenseParam]![FindLicense]) AND
((licenseMembers.LicenseEnd)>Now() Or (licenseMembers.LicenseEnd) Is Null)
AND ((MemberStatus.MemberStatusID) In (1,6)));

This query limits the licenses just right.

I thought I could just convert this sql to sql that VBA would understand,
but I can't get it quite right.

I started with a smaller query to simplify things, because all I need in
"Contacts" is the email address (and because I could get str sql to work
using the LicenseQuery)-
ContactView query:
SELECT Contacts.ContactID, Contacts.EmailName1, Contacts.MemberStatusID,
FROM Contacts
WHERE (((Contacts.EmailName1) Is Not Null) AND ((Contacts.MemberStatusID) In
(1,6)));

Here's my str sql:
sql = "SELECT ContactView.EmailName1 FROM LicenseMembers INNER JOIN " +
"ContactView ON LicenseMembers.ContactID = ContactView.ContactID " + "WHERE
(((LicenseMembers.LicenseID)=" + CStr(Me.FindLicense) + ") AND
((LicenseMembers.LicenseEnd) Is Null) OR (LicenseMembers.LicenseEnd)>Now());"

I'm prompted to chose the license parameter, and then the code ignores the
parameter and creates an email. I can't quite figure out how it is
determining who to include on the email list or what I've done wrong. I have
similar email generation VBA statements and they work fine. Little comfort!
I'd appreciate your suggestions. Thanks.
 
T

tina

try

sql = "SELECT ContactView.EmailName1 " _
& "FROM LicenseMembers INNER JOIN " _
& "ContactView ON " _
& "LicenseMembers.ContactID = " _
& "ContactView.ContactID WHERE " _
& "LicenseMembers.LicenseID = " _
& CStr(Me!FindLicense) & " AND (" _
& "LicenseMembers.LicenseEnd Is Null OR " _
& "LicenseMembers.LicenseEnd > #" & Date & "#)"

note that i used Date instead of Now, because Now returns the current date
AND time (hour, minute, second), while Date returns only the current date.

hth


Stephanie said:
Hi. I am able to loop through my records and create an email, EXCEPT I'm not
limiting my email addresses based on one of the parameters.

I have Contacts that hold Licenses. I want to send an email to all Contacts
(that are in good standing) and have a current license as stated in the input
parameter- this is the part that is not working.

The set up: Contacts hold contact info, Licenses hold license info,
LicenseMembers is the many-to-one linking table.

I have a LiceseQuery that works:
SELECT Contacts.ContactID, Contacts.EmailName1, nz([NickName],[FirstName]) &
" " & [LastName] AS [Member Name], Licenses.LicenseName,
licenseMembers.LicenseID, licenseMembers.LicenseStart,
licenseMembers.LicenseEnd, Contacts.StateOrProvince1,
MemberStatus.MemberStatusID
FROM (Licenses INNER JOIN (Contacts INNER JOIN licenseMembers ON
Contacts.ContactID = licenseMembers.ContactID) ON Licenses.LicenseID =
licenseMembers.LicenseID) INNER JOIN MemberStatus ON Contacts.MemberStatusID
= MemberStatus.MemberStatusID
WHERE (((Licenses.LicenseName)=[forms]![LicenseParam]![FindLicense]) AND
((licenseMembers.LicenseEnd)>Now() Or (licenseMembers.LicenseEnd) Is Null)
AND ((MemberStatus.MemberStatusID) In (1,6)));

This query limits the licenses just right.

I thought I could just convert this sql to sql that VBA would understand,
but I can't get it quite right.

I started with a smaller query to simplify things, because all I need in
"Contacts" is the email address (and because I could get str sql to work
using the LicenseQuery)-
ContactView query:
SELECT Contacts.ContactID, Contacts.EmailName1, Contacts.MemberStatusID,
FROM Contacts
WHERE (((Contacts.EmailName1) Is Not Null) AND ((Contacts.MemberStatusID) In
(1,6)));

Here's my str sql:
sql = "SELECT ContactView.EmailName1 FROM LicenseMembers INNER JOIN " +
"ContactView ON LicenseMembers.ContactID = ContactView.ContactID " + "WHERE
(((LicenseMembers.LicenseID)=" + CStr(Me.FindLicense) + ") AND
((LicenseMembers.LicenseEnd) Is Null) OR (LicenseMembers.LicenseEnd)>Now());"

I'm prompted to chose the license parameter, and then the code ignores the
parameter and creates an email. I can't quite figure out how it is
determining who to include on the email list or what I've done wrong. I have
similar email generation VBA statements and they work fine. Little comfort!
I'd appreciate your suggestions. Thanks.
Click to expand...
 
S

Stephanie

Tina, You Rock!
Thanks for taking the time to run through a messy posting and for the
reminder of an easier method for listing sql and for the "date" vs. "now"
lesson. I actually had my parameter form backward (id vs. name) but couldn't
see that until you helped with the code. I appreciate your help!

tina said:
try

sql = "SELECT ContactView.EmailName1 " _
& "FROM LicenseMembers INNER JOIN " _
& "ContactView ON " _
& "LicenseMembers.ContactID = " _
& "ContactView.ContactID WHERE " _
& "LicenseMembers.LicenseID = " _
& CStr(Me!FindLicense) & " AND (" _
& "LicenseMembers.LicenseEnd Is Null OR " _
& "LicenseMembers.LicenseEnd > #" & Date & "#)"

note that i used Date instead of Now, because Now returns the current date
AND time (hour, minute, second), while Date returns only the current date.

hth


Stephanie said:
Hi. I am able to loop through my records and create an email, EXCEPT I'm not
limiting my email addresses based on one of the parameters.

I have Contacts that hold Licenses. I want to send an email to all Contacts
(that are in good standing) and have a current license as stated in the input
parameter- this is the part that is not working.

The set up: Contacts hold contact info, Licenses hold license info,
LicenseMembers is the many-to-one linking table.

I have a LiceseQuery that works:
SELECT Contacts.ContactID, Contacts.EmailName1, nz([NickName],[FirstName]) &
" " & [LastName] AS [Member Name], Licenses.LicenseName,
licenseMembers.LicenseID, licenseMembers.LicenseStart,
licenseMembers.LicenseEnd, Contacts.StateOrProvince1,
MemberStatus.MemberStatusID
FROM (Licenses INNER JOIN (Contacts INNER JOIN licenseMembers ON
Contacts.ContactID = licenseMembers.ContactID) ON Licenses.LicenseID =
licenseMembers.LicenseID) INNER JOIN MemberStatus ON Contacts.MemberStatusID
= MemberStatus.MemberStatusID
WHERE (((Licenses.LicenseName)=[forms]![LicenseParam]![FindLicense]) AND
((licenseMembers.LicenseEnd)>Now() Or (licenseMembers.LicenseEnd) Is Null)
AND ((MemberStatus.MemberStatusID) In (1,6)));

This query limits the licenses just right.

I thought I could just convert this sql to sql that VBA would understand,
but I can't get it quite right.

I started with a smaller query to simplify things, because all I need in
"Contacts" is the email address (and because I could get str sql to work
using the LicenseQuery)-
ContactView query:
SELECT Contacts.ContactID, Contacts.EmailName1, Contacts.MemberStatusID,
FROM Contacts
WHERE (((Contacts.EmailName1) Is Not Null) AND ((Contacts.MemberStatusID) In
(1,6)));

Here's my str sql:
sql = "SELECT ContactView.EmailName1 FROM LicenseMembers INNER JOIN " +
"ContactView ON LicenseMembers.ContactID = ContactView.ContactID " + "WHERE
(((LicenseMembers.LicenseID)=" + CStr(Me.FindLicense) + ") AND
((LicenseMembers.LicenseEnd) Is Null) OR (LicenseMembers.LicenseEnd)>Now());"

I'm prompted to chose the license parameter, and then the code ignores the
parameter and creates an email. I can't quite figure out how it is
determining who to include on the email list or what I've done wrong. I have
similar email generation VBA statements and they work fine. Little comfort!
I'd appreciate your suggestions. Thanks.
Click to expand...
Click to expand...
 
T

tina

Tina, You Rock!

....n' Roll - but that's dating me a bit, i know! ;)
and you're welcome :)


Stephanie said:
Tina, You Rock!
Thanks for taking the time to run through a messy posting and for the
reminder of an easier method for listing sql and for the "date" vs. "now"
lesson. I actually had my parameter form backward (id vs. name) but couldn't
see that until you helped with the code. I appreciate your help!

tina said:
try

sql = "SELECT ContactView.EmailName1 " _
& "FROM LicenseMembers INNER JOIN " _
& "ContactView ON " _
& "LicenseMembers.ContactID = " _
& "ContactView.ContactID WHERE " _
& "LicenseMembers.LicenseID = " _
& CStr(Me!FindLicense) & " AND (" _
& "LicenseMembers.LicenseEnd Is Null OR " _
& "LicenseMembers.LicenseEnd > #" & Date & "#)"

note that i used Date instead of Now, because Now returns the current date
AND time (hour, minute, second), while Date returns only the current date.

hth


Stephanie said:
Hi. I am able to loop through my records and create an email, EXCEPT
Click to expand...
I'm
not
limiting my email addresses based on one of the parameters.

I have Contacts that hold Licenses. I want to send an email to all Contacts
(that are in good standing) and have a current license as stated in
Click to expand...
the
input
parameter- this is the part that is not working.

The set up: Contacts hold contact info, Licenses hold license info,
LicenseMembers is the many-to-one linking table.

I have a LiceseQuery that works:
SELECT Contacts.ContactID, Contacts.EmailName1,
Click to expand...
nz([NickName],[FirstName])
&
" " & [LastName] AS [Member Name], Licenses.LicenseName,
licenseMembers.LicenseID, licenseMembers.LicenseStart,
licenseMembers.LicenseEnd, Contacts.StateOrProvince1,
MemberStatus.MemberStatusID
FROM (Licenses INNER JOIN (Contacts INNER JOIN licenseMembers ON
Contacts.ContactID = licenseMembers.ContactID) ON Licenses.LicenseID =
licenseMembers.LicenseID) INNER JOIN MemberStatus ON Contacts.MemberStatusID
= MemberStatus.MemberStatusID
WHERE (((Licenses.LicenseName)=[forms]![LicenseParam]![FindLicense]) AND
((licenseMembers.LicenseEnd)>Now() Or (licenseMembers.LicenseEnd) Is Null)
AND ((MemberStatus.MemberStatusID) In (1,6)));

This query limits the licenses just right.

I thought I could just convert this sql to sql that VBA would understand,
but I can't get it quite right.

I started with a smaller query to simplify things, because all I need in
"Contacts" is the email address (and because I could get str sql to work
using the LicenseQuery)-
ContactView query:
SELECT Contacts.ContactID, Contacts.EmailName1, Contacts.MemberStatusID,
FROM Contacts
WHERE (((Contacts.EmailName1) Is Not Null) AND
Click to expand...
((Contacts.MemberStatusID)
In
(1,6)));

Here's my str sql:
sql = "SELECT ContactView.EmailName1 FROM LicenseMembers INNER
Click to expand...
JOIN "
+
"ContactView ON LicenseMembers.ContactID = ContactView.ContactID " + "WHERE
(((LicenseMembers.LicenseID)=" + CStr(Me.FindLicense) + ") AND
((LicenseMembers.LicenseEnd) Is Null) OR (LicenseMembers.LicenseEnd)>Now());"

I'm prompted to chose the license parameter, and then the code ignores the
parameter and creates an email. I can't quite figure out how it is
determining who to include on the email list or what I've done wrong.
Click to expand...
I
have
similar email generation VBA statements and they work fine. Little comfort!
I'd appreciate your suggestions. Thanks.
Click to expand...
Click to expand...
Click to expand...
 

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

Union query- count records 2
Union without duplicates? 1
vb sql modification 2
sql syntax 3
invalid procedure call or argument 2
DAO recordset and DoCmd.SendObject 0
sql 7
parameter Query won't work 5

Top