SQL Syntax Issue MS Access VBA

Question

I'm having problems with the syntax: I transferred this from SQL Design View to VBA. [area] is numeric. I'm simply asking you guys to do a cursory review and tell me if anything Jumps out at you.

DoCmd.RunSQL "SELECT 'VCAP0112' AS VCAP0112, VCAP0112.[RECV IND] AS OMU, VCAP0112.[LEGACY ACCT], Sum(VCAP0112.[1 to 30 Day]) AS [0 - 30], " _
        & "Sum(VCAP0112.[31 to 60 Day]) AS [31 - 60], Sum(VCAP0112.[61 to 90 Day]) AS [61 - 90], Sum(VCAP0112.[91 to 120 Day]) AS [91 -  120], " _ 
        & "Sum(VCAP0112.[Greater than 120]) AS [+120], Sum(VCAP0112.[Pmt Difference]) AS TOTAL " _
        & "FROM Urcrosswalk INNER JOIN VCAP0112 ON Urcrosswalk.[Legacy GL] = VCAP0112.[LEGACY ACCT] " _
        & "WHERE (((Left([VCAP0112].[area], 2))=80 Or (Left([VCAP0112].[area], 2))= 81)) " _
        & "GROUP BY 'VCAP0112', VCAP0112.[RECV IND], VCAP0112.[LEGACY ACCT] " _
        & "HAVING (((VCAP0112.[RECV IND])='O' Or (VCAP0112.[RECV IND])='M' Or (VCAP0112.[RECV IND])='U'));"

HansUp · Accepted Answer · 2016-03-09 16:34:47Z

3

You can't use DoCmd.RunSQL with a plain SELECT query.

From the DoCmd.RunSQL Method help topic ...

A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to access another database.

If your intention is to view the results from that query in Datasheet View, create a saved query with its SQL.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSelect As String

strSelect = "SELECT ... blah, blah, blah"
Debug.Print strSelect '<- inspect this in Immediate window; Ctrl+g will take you there
Set db = CurrentDb
Set qdf = db.CreateQueryDef("YourQueryNameHere", strSelect)

Then you can open the saved query with DoCmd.OpenQuery ...

DoCmd.OpenQuery "YourQueryNameHere"

edited Mar 9, 2016 at 16:34

answered Mar 9, 2016 at 16:17

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

6 Comments

John Shaw Over a year ago

Thanxs @Hansup Sorry to bother. I'll go to msdn.microsoft.com/en-us/library/office/ff194626.aspx - before asking next time.

HansUp Over a year ago

No bother, John. Good to see you again. I don't understand why you would need to go to the web, though. Can't you use Access' built-in help?

HansUp Over a year ago

BTW, check out Debug.Print AccessError(2342) in the Immediate window. The template text for that error message includes more detail which would have clarified the situation at the outset. I don't know why the error dialog only showed you part of it. But AccessError() is another useful tool to keep in your toolbox. Regards.

John Shaw Over a year ago

While here at the Company, the built-in help isn't always helpful, so I usually disregard that feature.

HansUp Over a year ago

Your're welcome. Yes, it would have been useful if the error dialog showed you more of that message.

|

Collectives™ on Stack Overflow

SQL Syntax Issue MS Access VBA

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related