How do I link the Report Date Range Form to my report?

[60myles]

The Wizard which sets up the ledger Database system has a nifty "Report Data Range" form on one of the generated reports. I copied it to my database forms, and put the VBA code into the report code. The form pops up OK and gets the dates, which appear on the report line {="For Transactions Between ..., etc., but totally disregards the selection of dates for the report data. I know this is a dumb newbie question, but I have obviously left out an important step! How do I get the data from the form to look at my [TransactionDate] field, and print data between [Beginning Date] and [Ending Date]? (I can do it with a query OK but I would like to use the nifty form!)

 

[tempclerk]

You can either reference the same form in the query behind the report, or in the code that opens up the report add a where clause (something like Where [TransactionDate] between [Beginning Date] and [Ending Date]; I don't know the syntax, sorry)

 

[Rhonin]

I am going to assume that the fields on the form are labeled [BeginningDate] and [EndingDate].Under the Transaction date in the query put

Between Forms!FormName!BeginningDate and Forms!FormName!EndingDate

This should work.

 

[60myles]

Well, thanks for the suggestions - I'm not running this particular report from a query, as the report in the Ledger Sample doesn't seem to - In order to make mine work, I wrote a query, which worked OK, but with the query I lost the Form. I like the cosmetic aspect of the form, and I suppose it's only cosmetic, but I am told that's what VBA is all about, and I know very little of that!
Would a SQL line such as you have given me above, in the VBA code of the form, work?

 

[dawnd3]

In the criteria feild of the query used as the data source of the report is where you would put Rhonin's suggested code. If the report is based on a table and not a query, you can have it generate a query by going to the data source field in the report and clicking on the build button. I don't understand what you mean by "with the query, I lost the form" Can you explain that. Dawn

 

[60myles]

I think I get it - I'm just going to insert that code into the query instead of [What is Beginning Date] and [What is ending date] - Makes sense - I'll try it and feed back the results!

 

[BillMc]

Have you worked this out. If you need further assistance, I can help you with this. Just let me know. Bill Bill@bmcauley.com

 

[JaneInMA]

The following code works at the form level and is very nifty. I borrowed this from someone here - I can't remember who, but if they want to stand up I would thank them.
Private Sub ReportCmd_Click()
Dim RptName As String
Dim LinkFilter As String
Dim NumEntries As Integer

If (Me!BegDate > Me!EndDate) And (Not (IsNull(Me!EndDate))) Then
MsgBox "Error: Beginning date cannot be later than Ending date!", vbCritical
Me!BegDate.SetFocus
Exit Sub
End If

LinkFilter = ""

If Not (IsNull(Me!BegDate)) Then
If LinkFilter = "" Then
LinkFilter = "[Date] >= #" & Me!BegDate & "#"
Else
LinkFilter = LinkFilter & "And [Date] >= #" & Me!BegDate & "#"
End If
End If

If Not (IsNull(Me!EndDate)) Then
If LinkFilter = "" Then
LinkFilter = &quot;[Date] <= #&quot; & Me!EndDate & &quot;#&quot;
Else
LinkFilter = LinkFilter & &quot;And [Date] <= #&quot; & Me!EndDate & &quot;#&quot;
End If
End If

NumEntries = DCount(&quot;*&quot;, &quot;table1&quot;, LinkFilter)
If NumEntries < 1 Then
MsgBox &quot;There are no records which match the specified criteria.&quot;, vbInformation
Else
RptName = Me!ReportName
DoCmd.OpenReport RptName, acViewPreview, , LinkFilter
DoCmd.Close acForm, &quot;ReportForm&quot;
End If

End Sub

It give an alternate to putting the fields in the query allowing the report to be run for all dates or just a subset.

 

[60myles]

Yes, Yes, Yes - Thank you Jane, I typed this in to my Form code (so I could almost figure out what it was doing), changed the file names appropriately, and it works! - And Bill, thanks for your help - this post worked so I'm now happy! :-D