Query Criteria from Form Problem

R

Ray S.

I have a query that returns service_type, amount, and cost_center. I want to
be able to have the user type the filtering criteria in a text box in a form.
Suppose the user wants to search for service_types that are like PC Support.
I want him to be able to type PC Support in the text box and have that be
used to filter the items in the query.
The name of the text box is STFilter.

The WHERE clause in my query reads like this:
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "'" & "*" &
[Forms]![Form1]![STFilter] & "*" & "'"));

I've tried
Like Forms!Form1!STFilter
Like "*"&Forms!Form1!.STFilter&"*"
Like "'"&"*"&Forms!Form1!STFilter&"*"&"'"
Like """&"*"&Forms!Form1!STFilter&"*"&"""

and any permutation I can think of, but none returns anything. I can type
them into the immediate window while the box holds relevant information, say
PC Support.

The first returns PC Support
The second *PC Support*
The third '*PC Support*'
The fourth Run Time error 13 Type mismatch.

None works to filter anything in the query.
 
K

KARL DEWEY

Try this --
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "*" & [Forms]![Form1]![STFilter] &
"*" ));
 
R

Ray S.

That gives me results, but they are not filtered at all. I just get all the
data unfiltered.

KARL DEWEY said:
Try this --
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "*" & [Forms]![Form1]![STFilter] &
"*" ));

--
KARL DEWEY
Build a little - Test a little


Ray S. said:
I have a query that returns service_type, amount, and cost_center. I want to
be able to have the user type the filtering criteria in a text box in a form.
Suppose the user wants to search for service_types that are like PC Support.
I want him to be able to type PC Support in the text box and have that be
used to filter the items in the query.
The name of the text box is STFilter.

The WHERE clause in my query reads like this:
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "'" & "*" &
[Forms]![Form1]![STFilter] & "*" & "'"));

I've tried
Like Forms!Form1!STFilter
Like "*"&Forms!Form1!.STFilter&"*"
Like "'"&"*"&Forms!Form1!STFilter&"*"&"'"
Like """&"*"&Forms!Form1!STFilter&"*"&"""

and any permutation I can think of, but none returns anything. I can type
them into the immediate window while the box holds relevant information, say
PC Support.

The first returns PC Support
The second *PC Support*
The third '*PC Support*'
The fourth Run Time error 13 Type mismatch.

None works to filter anything in the query.
Click to expand...
Click to expand...
 
K

KARL DEWEY

You have to have the form open and enter data in it.
--
KARL DEWEY
Build a little - Test a little


Ray S. said:
That gives me results, but they are not filtered at all. I just get all the
data unfiltered.

KARL DEWEY said:
Try this --
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "*" & [Forms]![Form1]![STFilter] &
"*" ));

--
KARL DEWEY
Build a little - Test a little


Ray S. said:
I have a query that returns service_type, amount, and cost_center. I want to
be able to have the user type the filtering criteria in a text box in a form.
Suppose the user wants to search for service_types that are like PC Support.
I want him to be able to type PC Support in the text box and have that be
used to filter the items in the query.
The name of the text box is STFilter.

The WHERE clause in my query reads like this:
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "'" & "*" &
[Forms]![Form1]![STFilter] & "*" & "'"));

I've tried
Like Forms!Form1!STFilter
Like "*"&Forms!Form1!.STFilter&"*"
Like "'"&"*"&Forms!Form1!STFilter&"*"&"'"
Like """&"*"&Forms!Form1!STFilter&"*"&"""

and any permutation I can think of, but none returns anything. I can type
them into the immediate window while the box holds relevant information, say
PC Support.

The first returns PC Support
The second *PC Support*
The third '*PC Support*'
The fourth Run Time error 13 Type mismatch.

None works to filter anything in the query.
Click to expand...
Click to expand...
Click to expand...
 
R

Ray S.

correct-o-mundo - I know that much. Even open and with the data in the
box...zilch... I fiddled with it and I get a pop-up parameter box asking for
data, but as I said, the data is in the box in the open form (not in design
mode either).

KARL DEWEY said:
You have to have the form open and enter data in it.
--
KARL DEWEY
Build a little - Test a little


Ray S. said:
That gives me results, but they are not filtered at all. I just get all the
data unfiltered.

KARL DEWEY said:
Try this --
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "*" & [Forms]![Form1]![STFilter] &
"*" ));

--
KARL DEWEY
Build a little - Test a little


:

I have a query that returns service_type, amount, and cost_center. I want to
be able to have the user type the filtering criteria in a text box in a form.
Suppose the user wants to search for service_types that are like PC Support.
I want him to be able to type PC Support in the text box and have that be
used to filter the items in the query.
The name of the text box is STFilter.

The WHERE clause in my query reads like this:
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "'" & "*" &
[Forms]![Form1]![STFilter] & "*" & "'"));

I've tried
Like Forms!Form1!STFilter
Like "*"&Forms!Form1!.STFilter&"*"
Like "'"&"*"&Forms!Form1!STFilter&"*"&"'"
Like """&"*"&Forms!Form1!STFilter&"*"&"""

and any permutation I can think of, but none returns anything. I can type
them into the immediate window while the box holds relevant information, say
PC Support.

The first returns PC Support
The second *PC Support*
The third '*PC Support*'
The fourth Run Time error 13 Type mismatch.

None works to filter anything in the query.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I fiddled with it and I get a pop-up parameter box asking for data
What does the popup say - exact quote please?

Double check your typing. You have to have the names exactly match without
any extra spaces or puncuation.
--
KARL DEWEY
Build a little - Test a little


Ray S. said:
correct-o-mundo - I know that much. Even open and with the data in the
box...zilch... I fiddled with it and I get a pop-up parameter box asking for
data, but as I said, the data is in the box in the open form (not in design
mode either).

KARL DEWEY said:
You have to have the form open and enter data in it.
--
KARL DEWEY
Build a little - Test a little


Ray S. said:
That gives me results, but they are not filtered at all. I just get all the
data unfiltered.

:

Try this --
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "*" & [Forms]![Form1]![STFilter] &
"*" ));

--
KARL DEWEY
Build a little - Test a little


:

I have a query that returns service_type, amount, and cost_center. I want to
be able to have the user type the filtering criteria in a text box in a form.
Suppose the user wants to search for service_types that are like PC Support.
I want him to be able to type PC Support in the text box and have that be
used to filter the items in the query.
The name of the text box is STFilter.

The WHERE clause in my query reads like this:
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "'" & "*" &
[Forms]![Form1]![STFilter] & "*" & "'"));

I've tried
Like Forms!Form1!STFilter
Like "*"&Forms!Form1!.STFilter&"*"
Like "'"&"*"&Forms!Form1!STFilter&"*"&"'"
Like """&"*"&Forms!Form1!STFilter&"*"&"""

and any permutation I can think of, but none returns anything. I can type
them into the immediate window while the box holds relevant information, say
PC Support.

The first returns PC Support
The second *PC Support*
The third '*PC Support*'
The fourth Run Time error 13 Type mismatch.

None works to filter anything in the query.
Click to expand...
Click to expand...
Click to expand...
 
R

Ray S.

OK, I'm figuring it has something to do with the form. I added another text
box just to move the focus from the one where you type in the filtering
criteria. When I did that, the query worked. Any idea on what I can do in the
form to avoid having a second empty box?

KARL DEWEY said:
What does the popup say - exact quote please?

Double check your typing. You have to have the names exactly match without
any extra spaces or puncuation.
--
KARL DEWEY
Build a little - Test a little


Ray S. said:
correct-o-mundo - I know that much. Even open and with the data in the
box...zilch... I fiddled with it and I get a pop-up parameter box asking for
data, but as I said, the data is in the box in the open form (not in design
mode either).

KARL DEWEY said:
You have to have the form open and enter data in it.
--
KARL DEWEY
Build a little - Test a little


:

That gives me results, but they are not filtered at all. I just get all the
data unfiltered.

:

Try this --
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "*" & [Forms]![Form1]![STFilter] &
"*" ));

--
KARL DEWEY
Build a little - Test a little


:

I have a query that returns service_type, amount, and cost_center. I want to
be able to have the user type the filtering criteria in a text box in a form.
Suppose the user wants to search for service_types that are like PC Support.
I want him to be able to type PC Support in the text box and have that be
used to filter the items in the query.
The name of the text box is STFilter.

The WHERE clause in my query reads like this:
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "'" & "*" &
[Forms]![Form1]![STFilter] & "*" & "'"));

I've tried
Like Forms!Form1!STFilter
Like "*"&Forms!Form1!.STFilter&"*"
Like "'"&"*"&Forms!Form1!STFilter&"*"&"'"
Like """&"*"&Forms!Form1!STFilter&"*"&"""

and any permutation I can think of, but none returns anything. I can type
them into the immediate window while the box holds relevant information, say
PC Support.

The first returns PC Support
The second *PC Support*
The third '*PC Support*'
The fourth Run Time error 13 Type mismatch.

None works to filter anything in the query.
Click to expand...
Click to expand...
Click to expand...
 
R

Ray S.

Is there some way I can have the results of the query display right on the
form? I'm planning three buttons that can run three different queries, but
I'd really rather have the results display in a box or boxes right on the
form. Is that possible? I mean, I'd like to press a button on the form and
have the results display in a box on the form.

Ray S. said:
OK, I'm figuring it has something to do with the form. I added another text
box just to move the focus from the one where you type in the filtering
criteria. When I did that, the query worked. Any idea on what I can do in the
form to avoid having a second empty box?

KARL DEWEY said:
I fiddled with it and I get a pop-up parameter box asking for data
Click to expand...
What does the popup say - exact quote please?

Double check your typing. You have to have the names exactly match without
any extra spaces or puncuation.
--
KARL DEWEY
Build a little - Test a little


Ray S. said:
correct-o-mundo - I know that much. Even open and with the data in the
box...zilch... I fiddled with it and I get a pop-up parameter box asking for
data, but as I said, the data is in the box in the open form (not in design
mode either).

:

You have to have the form open and enter data in it.
--
KARL DEWEY
Build a little - Test a little


:

That gives me results, but they are not filtered at all. I just get all the
data unfiltered.

:

Try this --
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "*" & [Forms]![Form1]![STFilter] &
"*" ));

--
KARL DEWEY
Build a little - Test a little


:

I have a query that returns service_type, amount, and cost_center. I want to
be able to have the user type the filtering criteria in a text box in a form.
Suppose the user wants to search for service_types that are like PC Support.
I want him to be able to type PC Support in the text box and have that be
used to filter the items in the query.
The name of the text box is STFilter.

The WHERE clause in my query reads like this:
WHERE (((DE_BG_Model.SERVICE_TYPE) Like "'" & "*" &
[Forms]![Form1]![STFilter] & "*" & "'"));

I've tried
Like Forms!Form1!STFilter
Like "*"&Forms!Form1!.STFilter&"*"
Like "'"&"*"&Forms!Form1!STFilter&"*"&"'"
Like """&"*"&Forms!Form1!STFilter&"*"&"""

and any permutation I can think of, but none returns anything. I can type
them into the immediate window while the box holds relevant information, say
PC Support.

The first returns PC Support
The second *PC Support*
The third '*PC Support*'
The fourth Run Time error 13 Type mismatch.

None works to filter anything in the query.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameters query 1
Problem with Criteria 12
Problem with Query Criteria by IIF/Switch 1
Parameter query where blank is like "*" 2
Query criteria pulling from a form 3
Using different form strings to filter 4
Using IN( ) function in a query 8
Multiple Parameter Crosstab query filter by form 1

Top