Create a Search Form and Generate a Report based on the Search Form

[ace]

I'm pretty new at Access (I'm working in Access 2002) and I would
really appreciate some advice for creating a search form that will
eventually generate a report. I would like for the user to have the
ability to enter up to five different search criteria on the form:

1. City
2. State
3. Property Type
4. Date (a range from a starting date to ending date)
5. Loan/Unit (also a range from a low loan/unit to high loan/unit)

Once the user clicks the "Search" button, I would like for Access to
create a "filtered" report based on the criteria entered by the user,
i.e. the user could specify a "State" and "Date Range" for the report
or the user could enter information for all the search fields on the
form. I have created the form and the report, but I have no idea how
to write the code for the "On Click" event procedure for the command
button. Can anyone offer some ideas here? If I need to explain my
situation in more detail, then please let me know. Again, I would
really appreciate any help that you can provide.

Thanks,

R

 

[Allen Browne]

To get you started, see:
Limiting a Report to a Date Range
at:
http://members.iinet.net.au/~allenbrowne/casu-08.html

The 2nd method in that article explains how to create a form to handle the
date range, and build the WhereCondition string for the OpenReport action.

To take it further, you just need to add more to strWhere. Add " AND " to
the existing strings. Then code stuff like:

If Not IsNull(Me.City) Then
strWhere = strWhere & "([City] = """ & Me.txtCity & """) AND "
End If

At the end remove the trailing " AND " before applying the string:
If Len(strWhere) - 5 > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If