Dynamic query - how to pass it into a report

[Irina]

I am creating dynamic query - I have to pass it into report somehow to make
the report run it.
Here is the code for the query
Public Function sql_name()

Dim sql_code As String
If Not IsNull([Forms]![frmClientReportFields2]![Individual]) Then
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like " & _
[Forms]![frmClientReportFields2].[Individual] & "*"
Else
sql_code = "SELECT tblClientReport.* FROM tblClientReport;"
sql_name = sql_code

End If

End Function

and right now
I am getting error "Invalid argument"
when I do either one of these in the command button

Public Sub cmdRun_Click()
Dim stDocName As String
stDocName = "rptClientReport"

DoCmd.OpenReport stDocName, acViewPreview, sql_code
DoCmd.OutputTo acOutputQuery, sql_code, acFormatXLS, "C:\ClientReport.xls",
False

End Sub

thanks

 

[Stuart McCall]

I am creating dynamic query - I have to pass it into report somehow to make
the report run it.
Here is the code for the query
Public Function sql_name()

Dim sql_code As String
If Not IsNull([Forms]![frmClientReportFields2]![Individual]) Then
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like " & _
[Forms]![frmClientReportFields2].[Individual] & "*"
Else
sql_code = "SELECT tblClientReport.* FROM tblClientReport;"
sql_name = sql_code

End If

End Function

and right now
I am getting error "Invalid argument"
when I do either one of these in the command button

Public Sub cmdRun_Click()
Dim stDocName As String
stDocName = "rptClientReport"

DoCmd.OpenReport stDocName, acViewPreview, sql_code
DoCmd.OutputTo acOutputQuery, sql_code, acFormatXLS,
"C:\ClientReport.xls",
False

End Sub

thanks

In your OpenReport call, include sql_code as the OpenArgs parameter:

DoCmd.OpenReport stDocName, acViewPreview, , , ,sql_code

Then in the OnOpen event of your report:

Me.RecordSource = me.OpenArgs

 

[Douglas J. Steele]

Create a query with the following SQL:

SELECT tblClientReport.*_
FROM tblClientReport
WHERE (tblClientReport.Individual Like
[Forms]![frmClientReportFields2].[Individual] & "*")
OR ([Forms]![frmClientReportFields2].[Individual] IS NULL)

and use that query as the RecordSource for the report. No need to have the
rest of the code.