I need to type data in to several fields on a form and then run a query using
these field to find matching data from a table

Put the following expression in the criteria of your form:
Forms!MyForm!NameOfFieldOnMyForm

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1100 users have come to me from the newsgroups requesting help
(E-Mail Removed)


"malonic" <(E-Mail Removed)> wrote in message
news:93A87971-04FE-4014-875C-(E-Mail Removed)...
>I need to type data in to several fields on a form and then run a query
>using
> these field to find matching data from a table

On Tue, 14 Feb 2006 07:36:48 -0800, malonic wrote:

> I need to type data in to several fields on a form and then run a query using
> these field to find matching data from a table

Let's assume it is dates that you want to use as criteria.
Create an unbound form. Add 2 Text Controls.
Name one StartDate and the other EndDate.
Set the Format property of these controls to a valid date format.

Add a command button.
Code the button's click event:

DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name

Name this form 'ParamForm'.

In the query, in it's Date field's criteria line, write:

Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

When you want to run the query, open this form first and enter the
dates. Click the command button and the query will run without the
need for any further parameter entries.
When the query opens the form will close itself.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Are the individual criteria optional, e.g. if there are 3 controls on the
form might you want the user to be able to enter values into only one or two
of them rather than all three? If so test for each being NULL, e.g.

SELECT *
FROM MyTable
WHERE
(Field1 = Forms!MyForm!Control1 OR Forms!MyForm!Control1 IS NULL)
AND
(Field2 = Forms!MyForm!Control2 OR Forms!MyForm!Control2 IS NULL)
AND
(Field3 = Forms!MyForm!Control3 OR Forms!MyForm!Control3 IS NULL);

Ken Sheridan
Stafford, England

"malonic" wrote:

> I need to type data in to several fields on a form and then run a query using
> these field to find matching data from a table

"PC Datasheet" <(E-Mail Removed)> schreef in bericht news:YznIf.15445$(E-Mail Removed)...
> Put the following expression in the criteria of your form:
> Forms!MyForm!NameOfFieldOnMyForm
>

?????
Wrong answer again ...

> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> Over 1100 users have come to me from the newsgroups requesting help
> (E-Mail Removed)

--
To Steve:
Over 370 users from the newsgroups have visited the website to read what kind of a 'resource' you are...

To the original poster:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

Fred:

If I may, I'd make a couple of points regarding parameters of date/time
data type.

1. Its advisable to declare the parameters in the query as DateTime. If a
date is entered in short date format in a control, using forward slashes as
the delimiters, it can be interpreted as an arithmetical expression rather
than a date. This will not raise an error but may return incorrect results,
as the arithmetical expression will evaluate to a value which corresponds to
the underlying value of a date/time (which Access implements as a 64 bit
floating point number as an offset from 30 December 1899 00:00:00).
Declaring the parameters in the query as DateTime will ensure that the values
are correctly interpreted.

2. Using a BETWEEN…AND operation to define a date range gives rise to
another potential problem in that it will fail to return rows on the final
day of the range if the column in question contains date/time values which
include a time of day.. This can be the case without the user being aware of
it if the values are viewed in a format without the times of day. The usual
cause of this is the use of the Now() function as the DefaultValue property
of a column. This can be avoided by means of a suitable CONSTRAINT on the
column but is rarely done so in my experience. Its prudent to assume,
therefore, that date/time values in a table might contain non-zero times of
day. Defining a date range as follows caters for this:

PARAMETERS
[Forms]![MyForm]![txtStartDate] DateTime,
[Forms]![MyForm]![txtEndDate] DateTime;
SELECT *
FROM Addresses
WHERE MyDate>=[Forms]![MyForm]![txtStartDate]
AND MyDate<[Forms]![MyForm]![txtEndDate]+1;

Ken Sheridan
Stafford, England

"fredg" wrote:

> On Tue, 14 Feb 2006 07:36:48 -0800, malonic wrote:
>
> > I need to type data in to several fields on a form and then run a query using
> > these field to find matching data from a table
>
> Let's assume it is dates that you want to use as criteria.
> Create an unbound form. Add 2 Text Controls.
> Name one StartDate and the other EndDate.
> Set the Format property of these controls to a valid date format.
>
> Add a command button.
> Code the button's click event:
>
> DoCmd.OpenQuery "QueryName"
> DoCmd.Close acForm, Me.Name
>
> Name this form 'ParamForm'.
>
> In the query, in it's Date field's criteria line, write:
>
> Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate
>
> When you want to run the query, open this form first and enter the
> dates. Click the command button and the query will run without the
> need for any further parameter entries.
> When the query opens the form will close itself.
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>

Fredg,

What if you don't want the form to close? It would be easier to close the
query after viewing the results and enter new criteria, especially if there
are many reports to run.
--
Lori


"fredg" wrote:

> On Tue, 14 Feb 2006 07:36:48 -0800, malonic wrote:
>
> > I need to type data in to several fields on a form and then run a query using
> > these field to find matching data from a table
>
> Let's assume it is dates that you want to use as criteria.
> Create an unbound form. Add 2 Text Controls.
> Name one StartDate and the other EndDate.
> Set the Format property of these controls to a valid date format.
>
> Add a command button.
> Code the button's click event:
>
> DoCmd.OpenQuery "QueryName"
> DoCmd.Close acForm, Me.Name
>
> Name this form 'ParamForm'.
>
> In the query, in it's Date field's criteria line, write:
>
> Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate
>
> When you want to run the query, open this form first and enter the
> dates. Click the command button and the query will run without the
> need for any further parameter entries.
> When the query opens the form will close itself.
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>

On Mon, 20 Feb 2006 13:28:27 -0800, Lori Burton wrote:

> Fredg,
>
> What if you don't want the form to close? It would be easier to close the
> query after viewing the results and enter new criteria, especially if there
> are many reports to run.

To not close the form, just leave this line out:
DoCmd.Close acForm, Me.Name
However, the form may be on top of the query blocking your view, so
you should at least hide it.
Change the DoCmd.Close acForm, "ParamForm"
to
Me.Visible = False


The original post to which I answered wanted to run a query, not a
report.
It would be done a bit differently if the query was the record source
for a report, and it was the report(s) you wanted to run.

Here's how to run multiple reports using the parameters entered into
the same form once.

We'll assume it is a Start and End date parameter you want.
Also that there are several reports run at one time, with one report
always the first and a different report that is always the final one.

Create an unbound form.

Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in each query's date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate

Next, code the first report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the last report's Close event:
' This is optional. you can always close the form manually.
DoCmd.Close acForm, "ParamForm

When ready to run the report, open the first report.
The form will open and wait for the entry of the starting and ending
dates wanted.
Click the command button and then the report will run.
The parameters will be available for all of the other reports.
When the last report closes, it will close the form.
If you want to keep the form open and view it instead of closing it,
after the last report is run, simply change
DoCmd.Close acForm, "ParamForm"
to
forms!ParamForm.Visible = True


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Many Thanks. All the answers were useful

"malonic" wrote:

> I need to type data in to several fields on a form and then run a query using
> these field to find matching data from a table

"malonic" wrote:

> Many Thanks. All the answers were useful
>
> "malonic" wrote:
>
> > I need to type data in to several fields on a form and then run a query using
> > these field to find matching data from a table

Another option to do this would be create your query with based off of your
table you want to poll from.
Then create a form with unbound text boxes to use for the query criteria.
Using the controls wizard place a command button on your form set to run your
query.
Next go back into your query and set your criteria as follows substituting
your table and form names:

(tblWorkOrders.[Work Order Number]) Like "*" &
[Forms]![frmWorkOrdersSearch]![Work Order Number] & "*" Or
tblWorkOrders.[Work Order Number] Is Null

For date ranges I added the date fields from my table to my query and then
created new fields in my query for each set of ranges I was wanting to be
able to poll from:

((tblWorkOrders![Actual Complete Date]) Between
[Forms]![frmWorkOrdersSearch]![From Actual Complete] And
[Forms]![frmWorkOrdersSearch]![To Actual Complete] Or
[Forms]![frmWorkOrdersSearch]![From Actual Complete] Is Null And
[Forms]![frmWorkOrdersSearch]![To Actual Complete] Is Null)

In the criteria for this field enter: <>False

I have this set up with six different data fields and six different from
and to date fields in my form and it works like a charm, can enter any
combination of criteria and or dates with results.

Hope this helps,