0

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!

aduguid
  • 3,099
  • 6
  • 18
  • 37
RGiesler
  • 23
  • 4
  • Welcome to Stack Overflow. `CAST` is not a supported function in Access SQL. – HansUp Jan 20 '15 at 15:27
  • 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 Jan 21 '15 at 11:11

2 Answers2

3

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.

Community
  • 1
  • 1
Rhys Jones
  • 5,348
  • 1
  • 23
  • 44
  • Thank you for your answer! After testing with both solutions from here, I went with editing my SQL-query in VBA, and it worked like a charm. The link you submitted has helped me out in some other aspects though, thank you! – RGiesler Jan 21 '15 at 11:13
2

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!

RGiesler
  • 23
  • 4