Complex SQL as RecordSource 1

[TimK01]

I construct a complex piece of SQL in code that basicly Selects by date summary data from 3 seperate Tables then joins it into a Union query.

The SQL produces exaclly the data I want but I now want to get that data into a report with headers and footers etc.

I have created a dummy table with the correct layout and a report over it

In the code I tried changing the Record Source property of the report to the constructe SQL, but couldnt manage it unless the report was in design mode then i couldnt change it to view mode.

Can anyone offer any insights on how to get my data onto paper

 

[ByteMyzer]

In the declarations section of your code module, type the following:

Public strSql As String


Following this statement should be the Sub or Function that creates the SQL String.

An example Code Module for this might read:

Option Compare Database
Option Explicit

Public strSql As String

Public Sub CreateSQL()

strSql="SELECT * FROM Table1;"

End Sub



Then, in the Report's Code Module, enter the following code:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = strSql
End Sub

 

[TimK01]

Excelent. that works a treat.

I just had to make one small change to qualify the reference to the sql string in the form
ie

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Forms![formname].strSql
End Sub
Thank you very much