Create Report Using Form Parameters

[Red Rider]

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?

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[Wayne]

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.


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?

Thanks Allen. That does sound like an easier way to approach this.