[AccessD] Create a Search

Virginia,
I've done something similar in a database recording visit information for a healthcare ministry.
There are over 70 geographic locations interfacing the database via the web.  Data is recorded for individual visits as well as for events where multiple people are seen, such as a health fair.  An Access front end of the database is used by the main office. 
There are three major report forms requesting information about visits, events, and a composite report.
The most complex is the visit summary report form.  This form has several criteria to choose from:  the client data (gender, income status, literacy, marital status, employment, age, education level, ethnic group, and language), the place of service (office, home, telephone, etc.), nurse, region, team, location, whether contact was the first visit or a follow-up, and even down to the individual person.  It also has the capability to choose the inclusive dates of the report (Thanks Drew, I use his calendar for the report form).  I also have a memo field to add comments to the report (the client usually has at least a page or two of comments).  On the form there is a text box that gives a continuous count of the number of records, so the user always knows how many records will be used in the report.
The user also has the ability to export the data into an Excel spreadsheet.  And finally, the user can save the criteria for the report (including comments) to regenerate the report at will.  I placed a combo box on the report form to "fill in" the criteria for any given report when selected.
In another database we use for resumes, the search form has three text fields that are used to search the text of a memo field.

All this to say:  the key to what you want to do is the criteria of the query.
Criteria for date fields:  between forms!frmSearch.datefield1 and forms!frmSearch.datefield2
Buttons to fill in the date fields for last quarter, last month, last week, yesterday, or today can be done.
For those text fields they want to type in, I would recommend a list box or combo box.
In the query, treat each field's search criteria separately.
Ensure the nulls or zero length strings are captured and treated like a "*" in the criteria.

Good luck.
Jim


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
Hollis,Virginia
Sent: Thursday, July 29, 2004 6:48 AM
To: 'accessD at databaseadvisors.com'
Subject: [AccessD] Create a Search


Does anyone have an example of creating a report by selecting search
criteria from fields? I had a request today to add a custom report builder
to one of my databases.

 

Some of the fields will be text where they can enter what they need and
other criteria will be from a combobox, they also want a date range field.
Then! they want to be able to save the criteria for later use.

 

For example - Document number - text box, Document type will be a combobox.
I want to know all the documents created during a certain month, quarter,
year, etc.).

 

I have tried something like this several times, but when it comes to having
more than one search criteria, I can never seem to make it work - then
adding a date on top of that - well, I am lost.

 

Thanks,

Virginia

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com