Solved VBA SQL string with DCount

[Gasman]

OK, this works

Select * from tblClient WHERE tblClient.ClientCMS IN (SELECT DISTINCT Emails.CMS 
FROM Emails
GROUP BY Emails.CMS
HAVING (((Count(Emails.CMS))>0)))

so try this, adjust for any of my errors

strSQL = "SELECT * FROM qryContacts "
StrWhere = "WHERE Contacts.ContactsID "
strCriteria = "(Select DISTINCT tbSales.ContactID from tblSales GROUP BY tblSales.ContactID HAVING (Count(tblSales.ContactID))>0)"

Select Case Me.optSales
    Case 1 'Default--All Contacts
        strWhere = ""
        strCriteria = ""
    Case 2 'Contacts with no sales
        strWhere =  strWhere & " NOT IN "
    Case 3 'Contacts with Sales
        strWhere =  strWhere & " IN "
          
End Select

strSql = strSQL & strWhere & strCriteria
Debug.Print strSQL

Me!subfrmList.Form.RecordSource = strSql

HTH

 

[Pat Hartman]

I just don't know how to use it to return contacts with sales or without sales without attaching the sales table and making it updatable.

A DCount() in the where clause doesn't involve a join if you want to only select customers with orders.

 

[Gasman]

@Pat Hartman
O/P then extended the question for those with and those without Sales and not even looking at Sales. : Standard I know.)

I went to bed last night thinking about this , and woke up this morning still thinking 'How would I do that' if I had to, hence my post.
Sad, I know but some things seem to stick with me?, this is one of them.

 

[Pat Hartman]

I get it. I have also spent many a sleepless night solving a problem

Using domain functions in queries is not efficient so I don't recommend this as a solution but using a dcount() in the where clause works. The only issue is, you have to use dynamic SQL since you can't change the relational condition from > 0 to = 0 on the fly.