MS Access VBA Runtime error 3075 on SQL Query with subquery

0
votes

I am encountering a problem in VBA, where the execution of an SQL-query generates Run-time Error 3075.

Running the same query in Microsoft SQL Server Manager, I encounter no problems and the desired result is returned.

I tried printing the sql-query with Debug.print (as suggested here: https://stackoverflow.com/a/3136299/4474367) to see what the query looks like - it looks ok and is working perfectly fine pasted into SQL Server Manager.

What I'm doing is selecting total count of rows (survey responses) for a certain Dealer (and some other criteria), dividing the result with the total amount of rows where A2 = 5 (which is selected with a subquery) and then multiplying with 100 to get percentage of A2 = 5.

What do you guys think is wrong? Are there any limitations in Access that I may not have taken consideration of? Only the sub-query is shown in the error-message.

Here is my query (I cut it up a little with line breaks to make it easier for reading):

SELECT DealerCode, 
    (SELECT CAST(COUNT(A2) AS FLOAT) 
    FROM dbo_Service 
    WHERE A2 = 5 
    AND MarketCode = 'CA' 
    AND DealerCode = '3140' 
    AND CompleteDate BETWEEN '2014-01-20' 
    AND '2015-01-20') / COUNT(*) * 100 AS TopBox 
FROM dbo_Service 
WHERE DealerCode = '3140' 
AND MarketCode = 'CA' 
AND CompleteDate BETWEEN '2014-01-20' AND '2015-01-20' 
GROUP BY DealerCode;

Uncut version (exactly the same as above, but without line breaks):

SELECT DealerCode, (SELECT CAST(COUNT(A2) AS FLOAT) FROM dbo_Service WHERE A2 = 5 AND MarketCode = 'CA' AND DealerCode = '3140' AND CompleteDate BETWEEN '2014-01-20' AND '2015-01-20') / COUNT(*) * 100 AS TopBox FROM dbo_Service WHERE DealerCode = '3140' AND MarketCode = 'CA' AND CompleteDate BETWEEN '2014-01-20' AND '2015-01-20' GROUP BY DealerCode;

VBA Snippet, query procedure:

strSQL = "SELECT DealerCode, (SELECT CAST(COUNT(A2) AS FLOAT) FROM dbo_Service WHERE A2 = 5 AND MarketCode = 'CA' AND DealerCode = '3140' AND CompleteDate BETWEEN '2014-01-20' AND '2015-01-20') / COUNT(*) * 100 AS TopBox FROM dbo_Service WHERE DealerCode = '3140' AND MarketCode = 'CA' AND CompleteDate BETWEEN '2014-01-20' AND '2015-01-20' GROUP BY DealerCode;"
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)
topBox = rs!topBox
db.Close

Here is a link to an image of the error I receive

screenshot1

Best Regards and thanks for reading!

2
sql-serverms-accessvbams-access-2010
Welcome to Stack Overflow. CAST is not a supported function in Access SQL. – HansUp
Thanks! That feels like something I really should have thought of a lot earlier, if there was a facepalm-smiley to add, I would most certainly use it now! Thanks again for your fast response! – RGiesler

2 Answers

3
votes

That's a SQL Server dialect query, not MS Access. You need to perform the query on SQL Server, a pass-through query. See SQL Server Passthrough query stored into an Access VBA recordset for more info and a solution.

2
votes

So, after following HansUp's answer, I edited my SQL-query and replaced CAST with Cdbl.

Now it looks like this:

"SELECT DealerCode, ((SELECT CDbl(COUNT(A2)) FROM dbo_Service WHERE A2 = 5 AND MarketCode = 'CA' AND DealerCode = '3140' AND CompleteDate BETWEEN '2014-01-20' AND '2015-01-20') / COUNT(*)) * 100 AS TopBox FROM dbo_Service WHERE DealerCode = '3140' AND MarketCode = 'CA' AND CompleteDate BETWEEN '2014-01-20' AND '2015-01-20' GROUP BY DealerCode;"

When changing to Cdbl instead of CAST I also had to remove "AS FLOAT" after the CAST.

So in short, it looks like this:

SELECT CDbl(COUNT(A2))

Instead of this:

SELECT CAST(COUNT(A2) AS FLOAT)

A quick fix that does the job!

Thanks for your answers!