0

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

Improve this question

2 Answers 2

Reset to default
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

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

6 Comments

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)?
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"))"
Dont worry, I am being an idiot. Reread your post and saw your double up the quotes!
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?
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: utteraccess.com/wiki/index.php/Recordsets_for_Beginners )
|
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 & "*") & ")"
Improve this answer

3 Comments

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!
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)
(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)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.