I have a form that creates folders and generates reports according to site. The user enters in
the date and where they want to report to be created at, hits the button and it starts creating
folders and reports.
I have two queries:
zqrySite - which displays all the sites. Just sites.
In query zqryBillingReportQuery, I created some criterias based on my form.
Under SiteName the criteria is : [Forms]![dlgMonthlyReport]![txtSite]
Under RecDate the criteria is : Between [Forms]![dlgMonthlyReport]![txtStartDate] And [Forms]![dlgMonthlyReport]![txtEndDate]
I declared each of these in the query parameter.
Everything works great here.. Just a brief history.
I have another form to create basically the same exact way. So I copy everything I did above and run the application. I go into each of my folder created and find out that my reports did not get seperated. I find out in the new query there are two dates to search between. One slight difference is the between date in the first query is on the criteria field. The second between date is in the OR field. I double checked with user and they need this second between date in that query.
Why is it not seperating the reports to the correct site?
I'll post the code behind the button if that helps any:
Private Sub cmdStart_Click()
Dim db As Database
Dim rstSites As Recordset
Dim rst As Recordset
Dim qdf As QueryDef
Dim strSQL As String
Dim intCount As Integer
Set db = CurrentDb()
Set rstSites = db.OpenRecordset("zqrySite", dbOpenDynaset)
Do While Not rstSites.EOF
Me!txtSite = rstSites("GenAlias")
strSQL = "select count (*) as cnt from zqryBillingReportQuery"
Set qdf = db.CreateQueryDef("", strSQL)
qdf.Parameters("[Forms]![dlgMonthlyReport]![txtStartDate]").Value = Me!txtStartDate
qdf.Parameters("[Forms]![dlgMonthlyReport]![txtEndDate]").Value = Me!txtEndDate
qdf.Parameters("[Forms]![dlgMonthlyReport]![txtSite]").Value = Me!txtSite
Set rst = qdf.OpenRecordset(dbOpenDynaset)
intCount = rst("cnt")
Set rst = Nothing
Set qdf = Nothing
If intCount > 0 Then
DoCmd.OutputTo acOutputReport, "zSRI BILLING REPORT", acFormatRTF, Me!txtLocation & _
Me!txtSite & ".rtf"
Thanks for your help... and sorry about the long post.