Adding SQL to VBA in Access

Question

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

score 1 · Accepted Answer · answered Dec 15 '16 at 14:25

1

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

answered Dec 15 '16 at 14:25

jleach

7,410
3
33
60

Hey, first of all thank you very much. This is going to be a button on my Access form which will essentially allow people to run a keyword search on some of the tables we have using a word they put in a text box. Just a quick query about what you said. Do I have to write the 'SQL=' on every line too ('Task =' in my example)? – Nicholas Dec 15 '16 at 14:31
I ask as its throwing a wobbly on my third line: Task = "WHERE (((GrantInformation.LeadJointFunder) = "Global Challenges Research Fund")) Or (((GrantInformation.Call) = "AMR large collab"))" – Nicholas Dec 15 '16 at 14:34
Dont worry, I am being an idiot. Reread your post and saw your double up the quotes! – Nicholas Dec 15 '16 at 14:35
Hey, sorry for bothering you. I tried to use your 'dim rs AS DAO.Recordset' and 'set rs = CurrentDb.OpenRecordset(Task)' code and it isnt doing anything. any ideas? – Nicholas Dec 15 '16 at 15:16
From there you would use the recordset however you need. If you want to apply that SQL statement to a form/combo RowSource/RecordSource, you can do so per your original snippet: `Me.RecordSource = sql` (for more info on recordsets, see here: http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners ) – jleach Dec 15 '16 at 15:20
Thank you again, you have been more than helpful – Nicholas Dec 15 '16 at 15:34

score 1 · Answer 2 · edited May 23 '17 at 10:30

1

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 & "*") & ")"

edited May 23 '17 at 10:30

Community

1
1

answered Dec 15 '16 at 14:36

Andre

26,751
7
36
80

Thank you, I got jdl134679's answer to make the SQL work (although now have to work out to get it to do what I need it to do :) I will however change my doubles to singles as you instructed! – Nicholas Dec 15 '16 at 14:39
2

Single quotes causes issues with certain names... `Where x = 'O'Reily'` for example. In either case (single or double quotes), the best practice is to convert any quotes in the string: `Where x = '" & Replace(y, "'", "''") & "'"` (unfortunately Access doesn't have a nice prepared statement/params system as you'd see in more robust frameworks, but you can write some helper functions to handle it fairly easily) – jleach Dec 15 '16 at 14:45
1

(just to be clear, this is an issue with double quotes as well... `Where x = "the word "this" is problematic"`, and they should likewise be escaped) – jleach Dec 15 '16 at 14:48

Adding SQL to VBA in Access

2 Answers2