By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Report from Query

P: 30
I am not seeing how to accomplish this:
I created a from that uses a subform, the main form has two fields, category and date.
After I select a category and date, the appropriate records show in the sub form
I would like to know how I can send this data to a report that I created using the subform data.
Right now when I produce the report it displays all the records, I would like to filter the report using the my form/subform data.
Dec 30 '08 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 489
In the record source for your report, Add criteria for your for your catagory and date. Base the criteria on your form. The code for the criteria would be something like below or you could just use the expression builder.

Expand|Select|Wrap|Line Numbers
  1. [forms]![YourFormName]![YourFieldName]
Replace YourFormName with the name of your form. Replace YourFieldName with the name of the field that you are basing the criteria on.
Dec 31 '08 #2

P: 30
I tried this but I keep getting [forms]![ScheduleLookup]![date] does not exist
(where Schedulelookup is my form and date is the field on the form

Is there maybe just a way to filter? where I can filter the report based on the two fields on my main form to return the data in the subform?

Dec 31 '08 #3

P: 30
I even tried this
strFilter = "[date]=" & Me![date] & " and [category] ='" & Me![category] & "'"
stDocName = "Schedule"
DoCmd.OpenReport stDocName, acPreview, , strFilter

But when I run the report I still get every record.
The immediate's window show the correct date and category
Dec 31 '08 #4

Expert 100+
P: 489
Is your form open?

Using "Date" as a field name is probally what's causing the problem. Date is a reserved word in access that represents the current date. You should change the field name on your form to something else like ScheduleLookupDate or whatever.
Dec 31 '08 #5

P: 30
strFilter = "[date]=#" & Me![date] & "# and [category] ='" & Me![category] & "'"
Dec 31 '08 #6

Expert Mod 15k+
P: 31,489
The format for the OpenReport method is :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport(ReportName, _
  2.                  View, _
  3.                  FilterName, _
  4.                  WhereCondition, _
  5.                  WindowMode, _
  6.                  OpenArgs)
Naturally you would expect the FilterName parameter to be what you need. It isn't!!

Set up a WhereCondition string which is like a SQL WHERE clause but without the "WHERE" keyword.

PS. It's not a good idea to get into the habit of updating the design of an object (report) simply to apply filtering.
Jan 7 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.