Solved VBA SQL string with DCount

[ClaraBarton]

Honestly, I've tried and tried... put quotes all over the place and this doesn't work

strWhere = "DCount("*","tblSales","[tblSales].[ContactsID] = 'Me![contactsID]'")>" & 0

 

[ClaraBarton]

I mean doesn't compile

 

[Gasman]

ContactID is likely to be numeric (well it would be in my DBs? ) so lose the quotes. they are for strings?
I'd also probably do that in two steps?.

intCount = "DCount("*","tblSales","ContactsID =" &  Me.contactsID)
strWhere = IntCount & " > 0"
Debug.Print strWhere

Use Debug.Print to see the result, and correct from that?

So look at the Debug.Print

Perhaps also explain what you are trying to achieve, as I do not believe that is what you want.?
I believe you would join the tblSales with your table by ContactsID and the use Count() ?

 

[ClaraBarton]

This is my query which works fine. However, I'd like to place the WHERE in my vba so that I can use options for limiting my list.
I cannot figure how to make a string from this WHERE

SELECT Contacts.ContactsID,
    Contacts.Company,
    Contacts.LastName,
    Contacts.FirstName, etc
FROM Contacts
WHERE (((DCount("*","tblSales","contactsID = " & [contacts].[contactsID]))>0));

intCount = "DCount("*","tblSales","ContactsID =" & Me.contactsID)

will not compile so I can debug it either.

 

[ClaraBarton]

oops... removed the "

 

[Gasman]

As I said, join on tblSales and count the ID there?
In fact one of the options is only where both records are present, so if you choose that, you will only get those that have a record in tblSales.?

 

[Gasman]

This works for me?
My Clients are your Contact and Emails your Sales.

I had to add a Count as all Clients will have an Email, but it was just to test the logic

SELECT tblClient.ClientName, tblClient.ClientCW, tblClient.ClientDivision, Count(Emails.ID) AS CountOfID
FROM tblClient INNER JOIN Emails ON tblClient.ClientCMS = Emails.CMS
GROUP BY tblClient.ClientName, tblClient.ClientCW, tblClient.ClientDivision
HAVING (((Count(Emails.ID))>5));

In fact the INNER JOIN was the join I was thinking of, option 1

 

[ClaraBarton]

I'm using qryContacts for several things so I want it only that table. I've been messing until I'm very confused. How do I correct this:

intCount = DCount("*", "tblSales", "ContactsID =" & Me!subfrmList.Form![ContactsID])

Select Case Me.optSales
            Case 1 'Default--All Contacts
                Me!subfrmList.Form.RecordSource = "qryContacts"
                Exit Sub
            Case 2 'Contacts with no sales
                strWhere = "intCount <" & 1
            Case 3 'Contacts with Sales
                strWhere = "intCount >" & 0
          
            End Select

strSql = "SELECT * FROM qryContacts " _
            & "WHERE " & strWhere

Me!subfrmList.Form.RecordSource = strSql

intCount is not a field so...

 

[Minty]

You are confusing yourself. intCount is a variable so add it (concatenate) to the string

strWhere = intCount & "< 1"

When you are struggling with these things add a simple

Debug.Print strSQL

After you have created it and you should see where you have possibly gone wrong in the immediate window in the VBA editor.

 

[Gasman]

If you are using DCount as I suggested, then you ONLY run the query if IntCount is > 0 ?
As you ALREADY know that ContactID has more than 0, (from the intCount variable) then you can just use the WHERE ID=ID logic.?

That was based on your initial code.

plus you can just hardcode the 1 and 0 at the end of your strWhere ?

strWhere = "intCount < 1"

etc

However I still do not think your approach is going to work as you will have

35 < 1
35 > 0

etc which makes no sense (to me at least?) You need the Me!subfrmList.Form![ContactsID]) in the WHERE clause.

I still think the join is needed/best option?

 

[Gasman]

Also is the Contacts.ContactID not the autonumber?

 

[ClaraBarton]

The whole reason I got into this mess is because the query becomes unupdateable when I add another table. I just want to know if there are sales or no sales or ignore sales completely

 

[Gasman]

The whole reason I got into this mess is because the query becomes unupdateable when I add another table. I just want to know if there are sales or no sales or ignore sales completely

Ok, but have you even tried my method.? Your query is/was a Select query, so now you are moving the goalposts?

It is not the most elegant method, but by using the DCount you identify if you want to process that ContactID?
If you do then you can just use the ContactID in your query as you have already established the other criteria with the DCount?

However you have not anwered my question 'is the Contacts.ContactID the autonumber?' If this was my database it would be?

 

[theDBguy]

The whole reason I got into this mess is because the query becomes unupdateable when I add another table. I just want to know if there are sales or no sales or ignore sales completely

Is this thread related to this one?

Solved - Grouped records are uneditable

I have contacts that are joined to sales. If there is more than one sale, then I get multiple records for each contact. I use a query that asks for the MIN(SalesID) but then because of this grouping the contacts are not editable. How can I fix this? Or what's a better way to ask my question to...

www.access-programmers.co.uk

 

[ClaraBarton]

yes the contactID is an autonumber. The intcount works. It returns a 1 or a 0 exactly as it should. 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.

 

[ClaraBarton]

yes it's related! I got the query to work. It worked great but I wanted to convert it to VBA and use my option control. Now I'm exhausted and cranky.

 

[ClaraBarton]

Nevermind. I'll go back and make the query work like it did. I need to try not being fancy.

 

[Gasman]

Well I think you are almost there.
As I said not the most elegant solution, but workable as you have used the criteria in two different steps.?
If Int =1 (would never be more, as it is the autonumber) then you have the sales
If Int = 0 No Sales

FWIW the inner join just on it's own is updatable Not sure how to get the non sales that way though. Subquery perhaps?
Having your DCount, you know which option to test and cancel if it fails.?

 

[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

 

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