Customizing Parameter Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a database that I have developed. My users are wanting a very user
friendly database. I would like to set up a form for the purpose of creating
a query and producing a report from such query. I would prefer to have the
report set up a certain way (as to match the format of previously created
reports).

I need to know how to create this form. I need to query data by a date
range (jan 1 to dec 31 of the same year) and also to limit this query to a
contact by either name of company or last name (some contacts have a last
name and some have a company name but not all of the contacts have both). I
would also like to be able to query by the date range and a certain field
(called hatchery). I would like to be able to have these on one form but am
uncertain if this is possible. I would like to be able to limit the dates to
valid dates. I could put the contact names/company names in a list/combo box.

The data to be queried by date is from a table. The contacts could be
obtained from a previous query I have already created.

Please help me and if possible limit coding as I am a novice.

Thanks in advance!!
 
Use the following criteria for your date range --
Between [Enter start date] and [Enter end date]
Put this criteria on two criteria rows. On one of the row also put this
criteria for Company --
Like [Enter company] & "*" (They only have to enter the first
part of the company name) OR if you are going to use the ListBox use this
--
[Forms]![YourFormName]![YourCompanyListBoxName]

On the second row put this criteria for the name --
Like [Enter last name of contact] & "*" (They only have to
enter the first part of the contact last name) OR if you are going to use
the ListBox use this --
[Forms]![YourFormName]![YourContactListBoxName]
 
The way I use multiple selections on a form to determine the correct
recordset is like this.

On the form put all your selection-boxes (checkbox/textbox/combobox
etc)

Make a query and put the corresponding fields in the query. For
instance: company-name. Put a combobox (named 'cbo_CompanyName') on
your form (named 'MyForm'). This combobox needs to list all the names
and have the correct column as bound column (I STRONGLY suggest to use
something like a company-id!) Then in your query:

SELECT * FROM MyTable
WHERE ( iif(isnull(myform.cbo_CompanyName),
true,[mytable].[company_name] = myform.cbo_CompanyName) = TRUE

(Hope I got the () right). What it does is this:

- check via the ISNULL()-function whether a user selected a value from
the combobox.
- if a value is selected, use this value to compare it to the table's
value. This will yield TRUE or FALSE depending of the comparison method
(You could use LIKE-operators etc etc).
- if the combobox has a NULL-value (so the user didn't use this
selection) then the result is always TRUE.

The whole expression is compared with TRUE. So if the
selection-criteria from the combobox was used AND it is found in the
table, the record will show. If the combobox is empty the record will
show.

When using multiple selection critera, you can just put them all in the
query. I made forms with about a dozen or more criteria.

You can play with the results using AND or OR operators with multiple
selections. AND"s will narrow down the resultset the more criteria you
use.

Good Luck
Hope this helps.
 
Back
Top