Passing a query parameter

[Tamer]

I'm about to lose my mind. I spent a long time trying to
pass an SQL statement to a query as follows. Somebody
please help, what am I missing?? It's giving me a syntax
error message for the SQL statement.



Dim db As DAO.Database
Dim rs As Recordset
Dim SQL As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

SQL = "SELECT [CaseName], [ChildName] FROM
[qry_UCRsFinal]" & "WHERE Supervisor = " & InputBox("Which
Supervisor?") & ";"

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

Set xlApp = CreateObject("excel.application")
With xlApp
.Visible = True
.WindowState = xlMinimized
End With
Set xlBook = xlApp.Workbooks.Add
xlBook.SaveAs FileName:="\\evolvserver\MIS\UCR
Tickler\UCRsSups"
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "UCRs By Supervisor"
xlSheet.Cells.Range("a2").CopyFromRecordset rs
xlBook.Save

Set xlBook = Nothing
Set xlSheet = Nothing
Set xlApp = Nothing
rs.Close
Set rs = Nothing
'Set prm = Nothing
'Set qdf = Nothing
'Set db = Nothing
End Sub

 

[Ken Snell [MVP]]

Two things come to my eyes:

Your original:
SQL = "SELECT [CaseName], [ChildName] FROM
[qry_UCRsFinal]" & "WHERE Supervisor = " & InputBox("Which
Supervisor?") & ";"

Note that you do not have a space in front of WHERE word.

Also, if Supervisor is a text-formatted field, you need to delimit the
InputBox's value with ' characters.

So, assuming that these are the correct problems, here is a modified string:
SQL = "SELECT [CaseName], [ChildName] FROM
[qry_UCRsFinal]" & " WHERE Supervisor = '" & InputBox("Which
Supervisor?") & "';"

 

[Tamer]

I don't know what to say to thank you. It worked!!

-----Original Message-----
Two things come to my eyes:

Your original:
SQL = "SELECT [CaseName], [ChildName] FROM
[qry_UCRsFinal]" & "WHERE Supervisor = " & InputBox("Which
Supervisor?") & ";"

Note that you do not have a space in front of WHERE word.

Also, if Supervisor is a text-formatted field, you need to delimit the
InputBox's value with ' characters.

So, assuming that these are the correct problems, here is a modified string:
SQL = "SELECT [CaseName], [ChildName] FROM
[qry_UCRsFinal]" & " WHERE Supervisor = '" & InputBox ("Which
Supervisor?") & "';"
--

Ken Snell
<MS ACCESS MVP>

I'm about to lose my mind. I spent a long time trying to
pass an SQL statement to a query as follows. Somebody
please help, what am I missing?? It's giving me a syntax
error message for the SQL statement.



Dim db As DAO.Database
Dim rs As Recordset
Dim SQL As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

SQL = "SELECT [CaseName], [ChildName] FROM
[qry_UCRsFinal]" & "WHERE Supervisor = " & InputBox ("Which
Supervisor?") & ";"

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

Set xlApp = CreateObject("excel.application")
With xlApp
.Visible = True
.WindowState = xlMinimized
End With
Set xlBook = xlApp.Workbooks.Add
xlBook.SaveAs FileName:="\\evolvserver\MIS\UCR
Tickler\UCRsSups"
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "UCRs By Supervisor"
xlSheet.Cells.Range("a2").CopyFromRecordset rs
xlBook.Save

Set xlBook = Nothing
Set xlSheet = Nothing
Set xlApp = Nothing
rs.Close
Set rs = Nothing
'Set prm = Nothing
'Set qdf = Nothing
'Set db = Nothing
End Sub

.