Open a Report by limiting the data based on a Search Form

L

Leslie

I have a union query (QRY_SearchUnion) that has combined several tables that
is the query for a search form. The form is working great and after the
information has been placed in the form and the user clicks the okCMD button
it opens to a search results form that shows all the items from all the
tables with the selected information. This part is working very well.

Then I created a report I've called Search Results, using the union query
fields, how do I get the report to open only using the search information
from the search results form rather than opening showing all the information
in the union query?

Thanks,
 
A

Allen Browne

Your command button uses OpenReport.
OpenReport has a WhereCondition argument to limit the results.

Build a string from the values you want to use to limit the report. The
field names for the columns will be those from the first SELECT in your
UNION query.

Couple of examples of creating a string for the WhereCondition:
Limiting a Report to a Date Range:
http://allenbrowne.com/casu-08.html
Print the record in the form:
http://allenbrowne.com/casu-15.html
 
A

Allen Browne

The site is hosted on a large, commerical server in the US, and the links
are working on this end of the planet. Please email me (using the address in
the signature below) if you still cannot reach them.
 
L

Leslie

Thanks for the reply, this time it worked, there must have been a server
glitch on my end of the US.
 
L

Leslie

I took your advise and it works great for the one item and limits the report,
but I have several text fields and one set of date fields that I'm working
with. How do I add to this code so that it works with all the fields and with
multiple fields on the form. For example, the Requestedby (this is working),
then their is an intention field, a AcceptorReject field, Description Field,
Explanation field. what do I do to add them to the list. They can use one
or several of the search fields.

Thank you for your help.
 
A

Allen Browne

Yes, you can combine several conditions into the WhereCondition string.

In the end, strWhere has to look like the WHERE clause of a query. To see an
example of what you are aiming to generate, mock up a query, and type any
old value in the Criteria row under each of the fields (RequestedBy,
AcceptorReject etc.) Then switch the query to SQL View (View menu), and look
at the WHERE clause.

Typically you want to ignore any boxes on your form that the user leaves
blank. You will therefore use this kind of thing:

Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.cboRequestedBy) Then 'Numeric field example.
strWhere = strWhere & "([RequestedBy] = " & Me.cboRequestedBy & ") AND "
End If

If Not IsNull(Me.txtDescription)) Then 'Text field example.
strWhere = strWhere & "([Description] = """ & Me.txtDescription & """)
AND "
End If

If Not IsNull(Me.txtRejectDate) Then 'Date field example.
strWhere = strWhere & "([RejcetDate] = " & _
Format(Me.txtRejectDate, conDateFormat) & ") AND "
End If

'etc for other controls.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere
 
Top