Using a form instead of a parameter query.

[Chris v.]

We are using Access 2007.
There are several parameters that my db users have to input to "filter" the
data that will complete reports for the agency. I have no trouble using date
fields on a form that allows the user to define the date range. However, the
users need to specify foreign key fields (long int.) and I wish them to enter
more than one value in the field at one time. Using the In () operator works
fine on the query itself, but how can I have the users type several integers
in a text box on a form that will form the parameter for the query?
Any ideas? And thanks for your help ... in advance.

 

[Allen Browne]

The simplest interface might be an unbound multi-select list box on your
form. Example in:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Alternatively, you could build the filter string with the IN operator.
Assuming a text box named txtNums where the user types the numbers separated
by commas:
strWhere = "([MyNumberField] IN (" & Me.txtNums & "))"
The trouble with that is the assumption that the user types the input
correctly. Perhaps you could test it with the Array() function.

Or you might want to write a more powerful parser, like the one Word uses in
the Print dialog for choosing the pages to print. The user can enter strings
like:
3, 5-8, 12

If you want to revert to doing it in a query, this is how it's done:
PARAMETERS WotNum Text ( 255 );
SELECT tClient.ClientNum
FROM tClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] & ",*";

 

[KARL DEWEY]

Here is one way --
Place form text box a fiels name in design view like this --
MyForm: [Forms]![YourFormName]![TextBox]

Use this as criteria --
Like "* " & [YourTable].[YourField] & ", *"

To enter the criteria in the form it must be entered starting with a space
and all must be followed with a comma and a space.

 

[Chris v.]

Karl, thanks for the quick reply. I've tried this, but the query returns no
values. I'm wondering if this is because the form's text box (type "text"?)
does not match the query field (long integer - it is a foreign key, the
primary key field of another table). Could that be the problem?

I have the "start date" and "end date" fields working fine from the input
form, simply by setting the query field's criteria to "Between
[forms]![inputFormName].[startDate] and [forms]![inputFormName].[endDate]."

Here is one way --
Place form text box a fiels name in design view like this --
MyForm: [Forms]![YourFormName]![TextBox]

Use this as criteria --
Like "* " & [YourTable].[YourField] & ", *"

To enter the criteria in the form it must be entered starting with a space
and all must be followed with a comma and a space.

--
Build a little, test a little.

We are using Access 2007.
There are several parameters that my db users have to input to "filter" the
data that will complete reports for the agency. I have no trouble using date
fields on a form that allows the user to define the date range. However, the
users need to specify foreign key fields (long int.) and I wish them to enter
more than one value in the field at one time. Using the In () operator works
fine on the query itself, but how can I have the users type several integers
in a text box on a form that will form the parameter for the query?
Any ideas? And thanks for your help ... in advance.

 

[Chris v.]

Allen thanks for the speedy reply. I read your reply carefully and have
looked at your site and the links. They may be appropriate for what I wish to
do, but are a bit advanced for me at this time. I will study them more, and
VBA also, and return for another look later. I appreciate your time and
references. Thanks again.

The simplest interface might be an unbound multi-select list box on your
form. Example in:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Alternatively, you could build the filter string with the IN operator.
Assuming a text box named txtNums where the user types the numbers separated
by commas:
strWhere = "([MyNumberField] IN (" & Me.txtNums & "))"
The trouble with that is the assumption that the user types the input
correctly. Perhaps you could test it with the Array() function.

Or you might want to write a more powerful parser, like the one Word uses in
the Print dialog for choosing the pages to print. The user can enter strings
like:
3, 5-8, 12

If you want to revert to doing it in a query, this is how it's done:
PARAMETERS WotNum Text ( 255 );
SELECT tClient.ClientNum
FROM tClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] & ",*";

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

We are using Access 2007.
There are several parameters that my db users have to input to "filter"
the
data that will complete reports for the agency. I have no trouble using
date
fields on a form that allows the user to define the date range. However,
the
users need to specify foreign key fields (long int.) and I wish them to
enter
more than one value in the field at one time. Using the In () operator
works
fine on the query itself, but how can I have the users type several
integers
in a text box on a form that will form the parameter for the query?
Any ideas? And thanks for your help ... in advance.

.