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?

 

[Pat Hartman]

I'd like to place the WHERE in my vba so that I can use options for limiting my list.

You cannot modify the structure of a querydef on the fly. If you want the where clause to be dynamic, you need to build the entire query in VBA. You can only substitute values for arguments.

Select ...
From ...
Where SomeField = Forms!yourformname!somecongrolname;

 

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

 

[Pat Hartman]

Did you try using a calculated field in the form as I suggested?
Did you try using a querydef that took an argument as I suggested?
Did you try fixing the syntax errors in your DCount() in the query?

Select...., DCount("*","tblSales","[tblSales].[ContactsID] = " & ContractID) as SalesCount
From sometable;

You can't use a count() in your query because that implies a HAVING clause and that means that the query has aggregated data and that means that the query is not updateable. The above query will be updateable and it will show a count of previous orders which sounds sufficient.

Queries that join multiple tables are not inherently not updateable. Queries that aggregate data are NOT UPDATEABLE. There are other ways to make a query not updateable and I'm sure someone can post a link for you.

 

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