MS Access SQL query within VBA code

Question

I need a second set of eyes on this SQL query embedded in the VBA code. I'm making an MS Access app that returns a dataset based on the to & from date criteria set by the user in the specific date picker boxes. The sql query that you see actually has been tested statically within MS Access query design view. I tested it with actual dates where you see the Me.from_filter and Me.to_filter. It worked perfectly! If you chose something like from 1/1/2015 to 5/1/2015 it returns columns of all the months you need. Perfect. Now when I embed it in the VBA code and assign it to a control variable, I get the "Run-time error '2342': A RunSQL action requires an argument consisting of an SQL statement." Can someone please eyeball this and tell me what might be wrong?

Option Compare Database
Option Explicit
Dim strSQL As String


Private Sub Command0_Click()
If IsNull(Me.from_filter) Or IsNull(Me.to_filter) Then
    MsgBox "You have not entered a start date or end date"
Else
    strSQL = "TRANSFORM Sum(dbo_ASSET_HISTORY.MARKET_VALUE) AS SumOfMARKET_VALUE " _
            & "SELECT [dbo_FIRM]![NAME] AS [FIRM NAME], dbo_FUND.CUSIP, dbo_FUND.FUND_NAME, dbo_FUND.PRODUCT_NAME " _
            & "FROM (dbo_ASSET_HISTORY INNER JOIN dbo_FIRM ON dbo_ASSET_HISTORY.FIRM_ID = dbo_FIRM.FIRM_ID) INNER JOIN dbo_FUND ON dbo_ASSET_HISTORY.FUND = dbo_FUND.FUND " _
            & "WHERE (((dbo_FIRM.Name) Like 'Voya F*') And ((dbo_ASSET_HISTORY.PROCESS_DATE) >= #" & Me.from_filter & "# And (dbo_ASSET_HISTORY.PROCESS_DATE) <= #" & Me.to_filter & "#)) " _
            & "GROUP BY [dbo_FIRM]![NAME], dbo_FUND.CUSIP, dbo_FUND.FUND_NAME, dbo_FUND.PRODUCT_NAME " _
            & "PIVOT [dbo_ASSET_HISTORY]![ASSET_YEAR] & '-' & [dbo_ASSET_HISTORY]![ASSET_MONTH];"
    DoCmd.RunSQL (strSQL)

End If
End Sub

Jevl · Accepted Answer · 2016-03-31 20:08:20Z

3

RunSQL is for running action queries like update, insert, select into, delete, etc as per Microsoft definition https://msdn.microsoft.com/en-us/library/office/ff194626.aspx , you should probably use OpenQuery or similar to run your query.

answered Mar 31, 2016 at 20:08

Jevl

9929 silver badges8 bronze badges

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

1 Comment

marlan Over a year ago

Or CurrentDB.Execute strSQL

Collectives™ on Stack Overflow

MS Access SQL query within VBA code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related