Method 'Open' of object'_Recordset' failed in SQL

Question

I'm looking to create an SQL query that will return the Case Manager, total number of activity & count of description that begins with 'Outgoing Calls'.

At the moment, I run this and get an error message saying Method 'Open' of object'_Recordset' failed. I'm sure it's because of 'Outgoing Calls' because it works fine when I remove it.

Can anyone see why this is failing, or know any alternative way of counting the number of descriptions that begin with Outgoing Calls? Bear in mind that Case method does not work on VBA.

SELECT m.CaseManager, 
SUM(m.Lead) AS 'Total Leads', 
COUNT(a.Description) AS 'Total Activity', 
COUNT(IF a.Description LIKE 'Outgoing Calls%' THEN 1 ENDIF) AS 'Outbound Calls' 
FROM AccountTable m INNER JOIN ActivityTable a 
ON m.AccountNumber=a.AccountNumber 
GROUP BY m.CaseManager;

Try executing the query directly in Access instead of from VBA code: you might get a more meaningful error message. The recordset is failing to open because of a syntax error in the SQL; the SQL editor should give better clues about what's wrong with it. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 31, 2021 at 13:51

FunThomas · Accepted Answer · 2021-03-31 14:11:15Z

1

(1) The statement if IIF, not IF (2) The IIF-statement needs an ELSE-branch

cannot test it for the moment, but statement should look like

SUM(IIF(a.Description LIKE 'Outgoing Calls*', 1, 0)) AS 'Outbound Calls'

https://support.microsoft.com/en-us/office/iif-function-32436ecf-c629-48a3-9900-647539c764e3

edited Mar 31, 2021 at 14:11

answered Mar 31, 2021 at 13:35

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user11091170 Over a year ago

Thanks - I'm unfortunately seeing the same error message as above when I add this.

Mathieu Guindon Over a year ago

@DannyO'Sullivan try IIF(a.Description LIKE 'Outgoing Calls%',1,0). IIF is a function that takes arguments, not a statement. You're mixing up IIF function and SELECT CASE statements; CASE WHEN a.Description LIKE 'Outgoing Calls%' THEN 1 ELSE 0 END should also work (note: I don't know Access-SQL).

FunThomas Over a year ago

Sorry, mixed things up. Updated my answer

user11091170 Over a year ago

Thanks guys. Oddly enough, the query above returns the total number of activities rather than just the Outgoing calls. So essentially the IIF statement returns the same as COUNT(a.Description). (And I can confirm that it's not supposed to be the same count.

FunThomas Over a year ago

You need to use SUM instead of COUNT. COUNT counts the number of hits of a query. And the wildcard in Access-SQL is *, not '%'. Updated my answer one more time and hope it is correct now

|

Collectives™ on Stack Overflow

Method 'Open' of object'_Recordset' failed in SQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related