CODEWITHSUNDEEP
sqlvbams-access
Nicholas
Nicholas

Reputation: 3737

Adding SQL to VBA in Access

I have the following SQL code:

SELECT GrantInformation.GrantRefNumber, GrantInformation.GrantTitle, GrantInformation.StatusGeneral, GrantSummary.Summary
FROM GrantInformation LEFT JOIN GrantSummary ON GrantInformation.GrantRefNumber = GrantSummary.GrantRefNumber
WHERE (((GrantInformation.LeadJointFunder) = "Global Challenges Research Fund")) Or (((GrantInformation.Call) = "AMR large collab")) 
GROUP BY GrantInformation.GrantRefNumber, GrantInformation.GrantTitle, GrantInformation.StatusGeneral, GrantSummary.Summary 
HAVING (((GrantSummary.Summary) Like ""*" & strsearch & "*"")) OR (((GrantSummary.Summary) Like ""*" & strsearch & "*""));

Which I want to insert into the following VBA:

Private Sub Command12_Click()

strsearch = Me.Text13.Value
Task =
Me.RecordSource = Task

End Sub

After 'Task ='.

However it keeps on returning a compile error, expects end of statement and half the SQL is in red. I have tried adding ' & _' to the end of each line but it still will not compile.

Any suggestions where I am going wrong? Many thanks

Upvotes: 0

Views: 183

Answers (2)

Andre
Andre

Reputation: 27644

I suggest to use single quotes inside your SQL string to not mess up the double quotes forming the string.

In my opinion it's a lot better readable than doubled double quotes.

Simplified:

Dim S As String
S = "SELECT foo FROM bar " & _
    "WHERE foo = 'Global Challenges Research Fund' " & _
    "HAVING (Summary Like '*" & strsearch & "*')"

Note the spaces at the end of each line.

Obligatory reading: How to debug dynamic SQL in VBA

Edit

To simplify handling user entry, I use

' Make a string safe to use in Sql: a'string --> 'a''string'
Public Function Sqlify(ByVal S As String) As String

    S = Replace(S, "'", "''")
    S = "'" & S & "'"
    Sqlify = S

End Function

then it's

    "HAVING (Summary Like " & Sqlify("*" & strsearch & "*") & ")"

Upvotes: 1

jleach
jleach

Reputation: 7800

You have to put the SQL into a string...

Dim sql As String
sql = "SELECT blah FROM blah;"

Note that this means you have to insert all of the values and double up quotes:

sql = "SELECT blah "
sql = sql & " FROM blah "
sql = sql & " WHERE blah = ""some value"" "
sql = sql & "  AND blah = """ & someVariable & """;"

After that, you have to do something with it. For SELECTs, open a recordset:

Dim rs AS DAO.Recordset
Set rs = CurrentDb.OpenRecordset(sql)

Or, for action queries, execute them:

CurrentDb.Execute sql, dbFailOnError

Without knowing how you plan to use it, we can't give much more info than that.

This conversion tool would be quite helpful for automating the process: http://allenbrowne.com/ser-71.html

Upvotes: 1

Related Questions