Re: Create Report Using Form Parameters

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

It might be easier to omit the WHERE clause from the query, and build a string to use in the WhereCondition of OpenReport. That would be simpler, more efficient, and solve other problems such as:
- Nulls being left out (e.g. records where Status is null won't be returned by your query);
- Mismatched numbers (e.g. if ID is numeric and the form asks for number 22, your query will also return records 220, 221, 2200, 2201, etc.)

If using the WhereCondition is new, here are some examples.

a) Print the record in the form:
http://allenbrowne.com/casu-15.html

b) Limiting a Report to a Date Range
http://allenbrowne.com/casu-08.html

c) Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The last one applies its filter to a form, but creating the filter string for a report is identical.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Red Rider" <handyman1600@xxxxxxxxx> wrote in message
news:8a31a2a6-d24b-4314-b0b2-207890f2c3ec@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a form that allows me to display various filtered data in a
subform which works great. I am trying to use this same filter
information to display a report but I can’t get it to work. It keeps
asking me to “Enter Parameter Value” and shows "tblProjects.Assigned
To" even though I’m already telling it what I want in this command:
DoCmd.OpenReport strDocName, acPreview, , "tblProjects.[Assigned To] =
5"
Evenly I want to replace the where section of this command with the
full where variable from the form but I'm just trying to get it to
work first so I know what it needs to display correctly.

The query the report is based on is:
SELECT tblProjects.ID, tblProjects.Title, Contacts![First Name] & " "
& Contacts![Last Name] AS [Assigned To], Contacts![First Name] & " " &
Contacts![Last Name] AS [Opened By], Manager![First Name] & " " &
Manager![Last Name] AS Manager, tblProjects.Priority, tblProjects.
[Planned Start], tblProjects.[Actual Start], tblProjects.[Planned
Completion], tblProjects.[Actual Completion], tblProjects.Status,
tblProjects.Description, tblProjects.Comment, Contacts.ID
FROM (Contacts INNER JOIN tblProjects ON Contacts.ID = tblProjects.
[Assigned To]) INNER JOIN Manager ON tblProjects.ID = Manager.ID
WHERE (((tblProjects.Title) Like IIf(IsNull([Forms]!
[frmSearchProjects]![Title]),"*",[Forms]![frmSearchProjects]![Title] &
"*")) AND ((tblProjects.Status) Like IIf(IsNull([Forms]!
[frmSearchProjects]![Status]),"*",[Forms]![frmSearchProjects]![Status]
& "*")) AND ((Contacts.ID) Like IIf(IsNull([Forms]![frmSearchProjects]!
[OpenedBy]),"*",[Forms]![frmSearchProjects]![OpenedBy] & "*") And
(Contacts.ID) Like IIf(IsNull([Forms]![frmSearchProjects]!
[AssignedTo]),"*",[Forms]![frmSearchProjects]![AssignedTo] & "*")));

What am I doing wrong?

.

Relevant Pages

  • Re: Use wildcard in combo box with multiple strings
    ... Add the field to the query then. ... filter by it, it has to be in the query... ... use the field to filter what rows are in the RecordSource for the report. ... The string you use as a filter must repeat the name of the field for ...
    (microsoft.public.access.forms)
  • Re: Need help applying SQL statement to Access2000 Query
    ... >WHERE clause, but with an ORDER BY clause. ... >construct the filter string and then open the main form (or report). ... to build the query and open the Main Form or a report ...
    (microsoft.public.access.formscoding)
  • Re: filtering reports
    ... Does this relate to the idea of the Filter or assigning the ... >I put in the code for the query supplied report ... >>If you need to combine two conditions, mock up a query ... > SQL View (View ...
    (microsoft.public.access.formscoding)
  • Re: Count Unique Values with sorting and filtering by pop-up form
    ... here is a sample of code I use to apply filter ... query criteria will be valid only when the report is loaded. ... > MS Access MVP ...
    (microsoft.public.access.reports)
  • Re: Report from a filtered form?
    ... is actually a lookup table (not lookup field). ... > problem filter active. ... > query of the report. ...
    (microsoft.public.access.reports)