473,327 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Pass information from a form to a query as criteria

84
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!text1. 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?
Nov 20 '06 #1
10 6176
NeoPa
32,556 Expert Mod 16PB
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.
Nov 20 '06 #2
sesling
84
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?
Nov 21 '06 #3
blyxx86
256 100+
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?
Nov 21 '06 #4
sesling
84
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?
Nov 21 '06 #5
blyxx86
256 100+
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?
Nov 21 '06 #6
sesling
84
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?
Nov 21 '06 #7
NeoPa
32,556 Expert Mod 16PB
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.
Nov 21 '06 #8
sesling
84
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;
Nov 22 '06 #9
NeoPa
32,556 Expert Mod 16PB
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
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String, strWhere As String
  2. Dim intWhere As Integer
  3.  
  4. strSQL = CurrentDB.QueryDefs("YourQueryName").SQL
  5. intWhere = InStr(1, strSQL, "WHERE ")
  6. strWhere = "WHERE (([Date]=" & FORMS!ACTIVITY!DATE & ") AND " & _
  7.             "([BRANCH]" & FORMS!ACTIVITY!BRANCH & "))"
  8. strSQL = Left(strSQL,intWhere) & strWhere
  9. 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.
Nov 22 '06 #10
sesling
84
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
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String, strWhere As String
  2. Dim intWhere As Integer
  3.  
  4. strSQL = CurrentDB.QueryDefs("YourQueryName").SQL
  5. intWhere = InStr(1, strSQL, "WHERE ")
  6. strWhere = "WHERE (([Date]=" & FORMS!ACTIVITY!DATE & ") AND " & _
  7.             "([BRANCH]" & FORMS!ACTIVITY!BRANCH & "))"
  8. strSQL = Left(strSQL,intWhere) & strWhere
  9. 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.
I still could not get the query to work the way I wanted. I did find a work around. I changed the text branch column to a value in my query "Val(Branch)". I created four text boxes on the form. I referenced those boxes in the query criteria section. The operators can now enter up to 4 branches to exclude from the query. This has been working for a few days now. I may need to add additonal boxes but this should not be an issue. Thanks for all your assistance.
Nov 30 '06 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Steve | last post by:
Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate field's criteria is set ats: Forms!FrmRestock!LastXDays. LastXDays on the form is a combobox where the selections are 30, 60...
2
by: neptune | last post by:
I built a form to access a query with a 2 field primary key. It should use 2 controls to find the unique record and display the other field values on the form. In the criteria section of the...
5
by: Don Seckler | last post by:
I have an update query that runs when a report closes. I have several reports that will need to run the update query with diferent criteria. I'd like to simply make the criteria change in the...
1
by: JM | last post by:
Hello, Using Access 2000 queries, you can reference(pass) form values directly using syntax like Forms!frmPaint!txtColor. I want to do a pass through query to SQL Server 2000, but I don't know...
0
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list...
3
by: dskillingstad | last post by:
I'd appreciate any help I can get. I'm not sure what I'm doing wrong, but.... I've searched these groups for some solutions but no luck. I have an unbound form (frmSearch), with several unbound...
0
by: Zlatko Matiæ | last post by:
Hi everybody! Recently I was struggling with client/server issues in MS Access/PostgreSQL combination. Although Access is intuitive and easy to use desktop database solution, many problems...
2
by: Bob Alston | last post by:
If you have an access form with record source being a straightforward query and where clause in the form definition, will the query be sent to the back end jet/Access database and executed there,...
10
by: Trevor2007 | last post by:
I have a query that setting date from and date to from form values works: >=!! And <=!! but now I am trying to add another peramiter to pass in adition to above and now it doesn't work, I get...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.