Excluding/Including Query of Single Record 1

[arentango]

Here's my table:

Employee Email Incl Email2 Incl2

The Incl is a checkbox value on a form that allows the user to include or not include an email address when the form creates a distribution list based on results.

I can get the SQL to determine from the Incl to only include the first email address ... but what if the primary email address does not want to be included, instead the second email adress does? My SQL wouldn't show the Email2 if the Incl was not checked.

Here's what my SQL looks like:

strSQL = "SELECT tblEmp.Email, tblEmp.Incl, tblEmp.Email2, tblEmp.Incl2 " _
"FROM tblEmp " & _
"WHERE ((tblEmp.Incl) = Forms!frmEmp!chkIncl.Value) " & _ 
"AND ((tblEmp.Incl) = Forms!frmEmp!chkIncl2.Value);"

 

[PHV]

Something like this ?
strSQL = "SELECT Email, Incl FROM tblEmp " & _
"WHERE Incl = Forms!frmEmp!chkIncl.Value " & _
"UNION SELECT Email2, Incl2 FROM tblEmp " & _
"WHERE Incl2 = Forms!frmEmp!chkIncl2.Value"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[arentango]

Thanks PHV, I think I see what you're saying - but i'm getting a syntax error for the two unions, am I missing something? I'm not familiar with SQL (learning while doing) so i don't completely understand the syntax that SQL uses.

strSQL = "SELECT Companies.ID, Companies.VA, " & _
"Companies.FHA, Companies.HE, Companies.Delivery, " & _
"People.[email], People.[email2], People.[Include], People.[Include2] " & _
"FROM Companies INNER JOIN People ON Companies.ID=People.ID " & _
"WHERE (((Companies.VA)=Forms!Email!chkVA.Value) " & _
"AND ((Companies.FHA)=Forms!Email.chkFHA.Value) " & _
"AND ((Companies.HE)=Forms!Email.chkHE.Value) " & _
"AND ((Companies.Delivery)=Forms!cmbDelivery.Value) " & _
[COLOR=red]"UNION SELECT People.Email, People.[Include] AND ((People.[Include])='-1') " & _
"UNION SELECT People.Email2, People.[Include2] AND ((People.[Include2])='-1');"[/color]

 

[PHV]

You may try this:
strSQL = "SELECT C.ID,C.VA,C.FHA,C.HE,C.Delivery,P.Email,P.Include " & _
"FROM Companies C INNER JOIN People P ON C.ID=P.ID " & _
"WHERE C.VA=Forms!Email!chkVA.Value " & _
"AND C.FHA=Forms!Email.chkFHA.Value " & _
"AND C.HE=Forms!Email.chkHE.Value " & _
"AND C.Delivery=Forms!cmbDelivery.Value " & _
"AND P.Include=True " & _
"UNION SELECT C.ID,C.VA,C.FHA,C.HE,C.Delivery,P.Email2,P.Include2 " & _
"FROM Companies C INNER JOIN People P ON C.ID=P.ID " & _
"WHERE C.VA=Forms!Email!chkVA.Value " & _
"AND C.FHA=Forms!Email.chkFHA.Value " & _
"AND C.HE=Forms!Email.chkHE.Value " & _
"AND C.Delivery=Forms!cmbDelivery.Value " & _
"AND P.Include2=True"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244