Creating a Report from a Query By Form using a strSQL

[bfuenz]

I have a form wherein I ask the user to type in a range of dates. Once the user does that and hits 'search', Access returns data, in a list box within my form, that is between the date ranges that the user typed in. I use the following code:

Private Sub Command22_Click()

Dim strSQL As String
Dim strSQL2 As String

strSQL = "SELECT tblUsers.ID, tblUsers.Birthday, tblUsers.Name FROM tblUsers WHERE [Birthday]>= #" & _
Me!StartDate & &quot;# AND [Birthday]<= #&quot; & Me!EndDate & &quot;# ORDER By tblUsers.Birthday DESC;&quot;

strSQL2 = &quot;SELECT COUNT([ID]) FROM tblUsers WHERE [Birthday]>= #&quot; & _
Me!StartDate & &quot;# AND [Birthday]<= #&quot; & Me!EndDate & &quot;#;&quot;

With Me
.List20.RowSource = strSQL
.List26.RowSource = strSQL2
End With

End Sub

My question: I want to be able to print out the results to a report. How do i do that?

I have tried using a public strQL that is used in the form and then by the report and it doesn't work. I have tried to create a temporary table that once it is created it is used by the report's recordsource property in order to populate the report but It still doesn't work.

Any suggestions?

 

[dynamictiger]

Try adding the same SQL Statements to the onOpen event of the report as the recordsource.

 

[bfuenz]

Hey DynamicTiger,

I tried placing the entire srtSQL on the onOpen properties of the report however, it gives me an error wherein it tells me that the 'start date' cannot be found. Meaning, the start date text box that is in the FORM is not being found in the report.

BFuenz

 

[bfuenz]

DynamicTiger,

Thanks for your post, I worked on it a little bit and it worked out. Thank you

BFuenz

 

[dynamictiger]

Just a thought. Having worked with reports a lot now, sometimes it is worth placing the values from the form into the report first and then doing any code work. Watch out for this, it is an easy error in report code to refer to form fields, whcih works but is not particularly efficent.