Pass information from a form to a query as criteria

[sesling]

I have created a query that will pull information from our database for the operators. This query will pull in on average 50,000 records. The operators need to refine the search results. I have used the following in the criteria section of the query for the operators to refine the search [Enter the Clients Name:]. However, the operators do not always remember how the clients name appears in the DB and we get several failed queries. To correct this, I created a form with a pick list and a command button. The operator will select the clients name and run the query from the command button. In the query I replaced [Enter the Clients Name:] with the following; forms!form1!tex t1. The selected value in text1 from the form gets passed to the query and the results are returned. This will shrink the results to maybe 2000 records. However, we still need to refine the search even further. One of the fields in the query stores branch numbers. The field in the table is a text field. The operator needs to exclude a certain group of branches from the results. On the form I created a text box for them to enter the branches to exclude. I have them entering
Not In ("59","99","78" ). When this gets passed to the query, Access puts single ' around the text and the query returns 0 records. If the Not In statement is placed directly in the query criteria field we get the proper results. How can I pass this type of information from a form to a query so we can refine the results?

[NeoPa]

If you tell us how you're building your SQL (or query) and how you're applying the filter we can help you with a modified version.
Your question doesn't make it clear what you're doing exactly.

[sesling]

If you tell us how you're building your SQL (or query) and how you're applying the filter we can help you with a modified version.
Your question doesn't make it clear what you're doing exactly.

Its a simple query that is based on one table that contains 15 columns of data. (14 columns are text based and 1 is a date). I am retrieving all 15 columns of data. I am currently filtering on two columns (Date and Name) and would like to add a third (Branch #). Branch numbers are from 00 - 99 and again stored as text

On the form (titled - Activity), the operator will enter a date in a text box (titled Date). In a separate text box the operator will enter the client name. (titled Name). In the query, date column criteria has the following -
forms!activity! date. The name column has the following - forms!activity! name.
When the operator launches the query from the form, it passes the date and name to the query and we get the results. I would like to add a filter for the branch number. We need to exclude certain branch numbers from the results to get at the data we are looking for.

I have tried the following with no luck. I created a new text box on the form (Titled Branch). In the query, branch column criteria has the following -
forms!activity! branch. I have tried passing in the following to filter on the branch number (keep in mind the branch number column is stored as text)

Not In ("59","99")
<> "59" and <> "99"

If either of the above filters are used from the form, we get 0 items returned.
I found that Access places single ' around the filters above and thats why I believe we are getting 0 records returned. If I use the filters above directly in the query criteria field we get the correct results. Is there another way to filter the branch number that will return results?

[blyxx86]

Just wondering... Why is a number being stored as text? You may want to try putting #59# around the numbers. And also might want to try putting a parameter for the numbers being calculated as text and not numbers.

Just a thought.

here is the sql for what the parameter would look like:
PARAMETERS forms!activity! branch Text ( 255 );
SELECT (your select clause)
FROM (etc..)
WHERE (etc...)

Its a simple query that is based on one table that contains 15 columns of data. (14 columns are text based and 1 is a date). I am retrieving all 15 columns of data. I am currently filtering on two columns (Date and Name) and would like to add a third (Branch #). Branch numbers are from 00 - 99 and again stored as text

On the form (titled - Activity), the operator will enter a date in a text box (titled Date). In a separate text box the operator will enter the client name. (titled Name). In the query, date column criteria has the following -
forms!activity! date. The name column has the following - forms!activity! name.
When the operator launches the query from the form, it passes the date and name to the query and we get the results. I would like to add a filter for the branch number. We need to exclude certain branch numbers from the results to get at the data we are looking for.

I have tried the following with no luck. I created a new text box on the form (Titled Branch). In the query, branch column criteria has the following -
forms!activity! branch. I have tried passing in the following to filter on the branch number (keep in mind the branch number column is stored as text)

Not In ("59","99")
<> "59" and <> "99"

If either of the above filters are used from the form, we get 0 items returned.
I found that Access places single ' around the filters above and thats why I believe we are getting 0 records returned. If I use the filters above directly in the query criteria field we get the correct results. Is there another way to filter the branch number that will return results?

[sesling]

Just wondering... Why is a number being stored as text? You may want to try putting #59# around the numbers. And also might want to try putting a parameter for the numbers being calculated as text and not numbers.

Just a thought.

here is the sql for what the parameter would look like:
PARAMETERS forms!activity! branch Text ( 255 );
SELECT (your select clause)
FROM (etc..)
WHERE (etc...)

The branch number is being stored as text because that is how is passed to us from the customer.

Thx for the help. I tried the # around the numbers and it did not work. I am not sure how to apply the other suggestion. Where would this statement be located? On the form or in the sql for the query?

[blyxx86]

Yes it is found in the SQL of the query.. If you right click up where the tables for the query are shown, you can select 'parameters' and set the parameter and the type.

The branch number is being stored as text because that is how is passed to us from the customer.

Thx for the help. I tried the # around the numbers and it did not work. I am not sure how to apply the other suggestion. Where would this statement be located? On the form or in the sql for the query?

[sesling]

Yes it is found in the SQL of the query.. If you right click up where the tables for the query are shown, you can select 'parameters' and set the parameter and the type.

I added the parameter statement and still get zero records. What should we be entering in the text box on the form that will be passed to the query?

[NeoPa]

When in a designed query (QueryDef) you enter a reference to a field on a form for your criteria, it simply compares the data with the value in the form's field.
NB It doesn't provide a means of changing the query generally.
If you want to change the actual SQL (Seems necessary here) it gets more complicated.
You need to either :
1. Copy your existing query to another (temp) name then set the .SQL property after modifying a string with SQL grabbed initially from the query.
2. Create a new temporary query and set the SQL as in 1 above.

If no-one comes up with a simpler solution, then I'll dig up details of the basics of this approach for you.

[sesling]

When in a designed query (QueryDef) you enter a reference to a field on a form for your criteria, it simply compares the data with the value in the form's field.
NB It doesn't provide a means of changing the query generally.
If you want to change the actual SQL (Seems necessary here) it gets more complicated.
You need to either :
1. Copy your existing query to another (temp) name then set the .SQL property after modifying a string with SQL grabbed initially from the query.
2. Create a new temporary query and set the SQL as in 1 above.

If no-one comes up with a simpler solution, then I'll dig up details of the basics of this approach for you.

FYI, I am not sure this will help but here is the query I am currently using. On the form, the operator can only enter 1 branch number at a time. This restricts the info the operators get. They need to be able to see multiple branch at the same time. I figure the exclude option would be better since we only need to exclude three or four versus including 30 to 50.

SELECT DATE, CLIENT_NAME, BRANCH, ADDRESS, CITY, STATE, ZIP, COUNTRY, TRACE, RESEARCH, OPERATOR,
OPID, ORDERCODE, DESC1, DESC2, DESC3
FROM PENDING_ORDERS
WHERE DATE = FORMS!ACTIVITY! DATE
AND CLIENT_NAME = FORMS!ACTIVITY! NAME
AND BRANCH = FORMS!ACTIVITY! BRANCH;

[NeoPa]

In your command Button code (OnClick event)
You need some code that will modify your SQL.
Then you need to have a temp query to put the new SQL in.
If you put it in the original query then this will be changed for next time (Unpredictable) .

Something along the lines of

Dim strSQL As String, strWhere As String
Dim intWhere As Integer
 
strSQL = CurrentDB.QueryDefs("YourQueryName").SQL
intWhere = InStr(1, strSQL, "WHERE ")
strWhere = "WHERE (([Date]=" & FORMS!ACTIVITY!DATE & ") AND " & _
            "([BRANCH]" & FORMS!ACTIVITY!BRANCH & "))"
strSQL = Left(strSQL,intWhere) & strWhere
CurrentDB.QueryDefs("TempQueryName").SQL = strSQL

You'll have to fill in the gaps. I haven't time to do it all for you, but this is the idea.
Have fun.