I am able to filter records based on certain criteria. How do I then create a report only on that data? When I try to create a report I get all records no matter what the filtered results were. Any help?

I assume you open the report from a button on a form. On the button's ON CLICK event, you code this:


if text or


if a number or


If a date...

What goes in "Me.txtMyField"? The field I'm sorting by is a date and it's name is "IssueDate1".

The field I'm sorting by is a date. What goes where you have a "#" symbol? Also, what do I put where it says "Me.txtMyField"? Thanks for your help!!

Use the date example like this:



Assuming the name of the field in the table is IssueDate1 and the name of the textbox is also IssueDate1. For example, if your table is tblMyTable and the date field in the table is dtmIssueDate1 and the textbox on the form is called txtIssueDate1, then

The # symbol is not a value, it is called a delimiter. You use it to surround dates when writing SQL statements with WHERE clauses.

For example: "SELECT [dtmIssueDate1] FROM tblMyTable WHERE [dtmIssueDate1] = #" & Date() & "#"

I got an error. It says, "Compile Error: Method or data member not found". The highlighted text is ".txtIssueDate1". "Issue Date1" is the name of the text box. Thanks for your patience.

I tried it this way too.

DoCmd.OpenReport "Fund Development Log", acViewPreview, , "[IssueDate1] = #" & Me.IssueDate1 & "#"

With this I got the error, "Syntax error in date in query expression '([Issue Date1] = ##)"

Clint

This error occured probably because the textbox you specified, Me.IssueDate1, is empty. Make sure the name of the textbox is really IssueDate1. Click on it once and view its name in the property window. Or if the name of the textbox is correct, but the date really IS blank, then try this:

Brilliant!

I had the same question! Thanks, Greg.

Sweet!! Thanks Greg! It works great.
Clint

Greg,

I'm finally not getting any errors anymore. Thanks! The problem I have now is that the report only creates a report based on the "active" date in the "IssueDate1" field. For example in the 8 filtered our records the report includes whatever current date is showing in the record I'm viewing on my computer screen. It filters all the records correctly, but the report doesn't include all the filtered results, only the date that I can see. I can change records and then create another report that has that date in it, but it won't include all the dates in the filtered results. What should I do about this? Thanks for your help so far.

Clint

You lost me there... please restate your problem more clearly. Thanks.

I have successfully been able to filter out records based on a certain date range thanks to your help. Now what I want to do is create a report based on the filtered data. I tried this already and failed. For example our of 156 records I filtered out 4 because they met the criteria. The active window says at the bottom, "1 of 4 (filtered". When I click on the report button and go to the report that shows this data only the filtered date that I'm actively looking at shows up in the report. In this example records 1-2 have the same date. They all show up in the report. Records 3-4 don't. If I go to record three and click on the report button, only records 3 and 4 show up because they have the same date. How do I get all the filtered records that met the date criteria to show up in the report?

Oh, you confused me because you're talking about filtering in two places... your form and your report. You filtered your FORM down to four records. Now when you open your REPORT, it's filtering on that date and only showing two out of four records. Essentially what you're doing is filtering twice.

I think what your original question should have been this. If you filter a FORM and open the REPORT, how do you reflect the same filter on the report? It sounded like in your original question that you did NOT filter the form and that you filtered the report based on a date on the form.

What you do is filter the form as you've been doing. Issue a DoCmd.OpenReport command on the button but do NOT specify a WHERE clause. This will open the report with everything in it.

On the report's ON OPEN event, you put this:



Change frmMyFormName to your actual form name. When the report opens (with everything), it'll immediately take on the same filter as the active form. In your case, you'll see the same four records (assuming the form and the report have the same underlying table or query).

I couldn't get this to work yet. The form I'm generating the report off of is a subform. Does that change things any? I keep getting the debugger for the second line of the code you offered even though I have changed it like you suggested.

If the report is getting the filter from a subform, then you'd do this:



Change frmMyMainFormName and sfrMySubFormName to the actual names.

It keeps giving me the error "expected end of statement" after I type in my form names. Is there another problem?

If your form names have spaces in them (a no-no), then you must surround the names with brackets.

Now I'm getting the error "Object doesn't support this property or method". Sorry for all the problems.

Try taking out the second .Form, that is...


Or moving it like this

Neither of those work either. I still get the same error. Any more ideas?

Im having the same problem. I have a Main Tab Form (on Page2) Called Diary and a Subform called mini Diary. I have 8 Buttons on the Main Diary Form which apply filters to the Subform and theywork well. But I don't seem to be able to create a button on the Main Form which will preview the single report as defined by the preselected filter applied by the button that the user clicks.

at the moment I have as my onClick event:

If Me.FilterOn And Len(Me.Filter & "") > 0 Then
DoCmd.OpenReport "DiaryList", acViewPreview, Me.Filter
Else
DoCmd.OpenReport "DiaryList", acViewPreview
End If

and on the openEvent of the report

Private Sub Report_Open(Cancel As Integer)
Me.FilterOn = True
Me.Filter = Forms.Diary.Form.MiniList.Filter
End Sub

and the same error keeps coming up 'object doesn't support this method or property'.

Advice is in abundance but there isn't much in the way of results on this occasion. If anyone has a solution, please advise.