Text box for query criteria (2003)

Topic

New Reply

I have a query that needs multiple criteria. I’ve created a form that has an unbound text box. This control receives up to four text entries from a list box. I can get the text box to show the four values as “value1” or “Value2” but, when I use this text box as the criteria in a query, no records show. I know I’m overlooking something. If there is only one value in the text box, the query works fine. Thanks in advance for any ideas.

Reply | Quote

Replies

Can you post the sql of the query. In design view of the query go to view SQL. Copy the sql and post it to a new post.

When the text box has multiple values, how do you separate the different values? Do you use spaces or punctuation? Is the query supposed to return values that match any one, or something else.

If you do a search, you will find posts on how to use a multi select listbox as a query criterion. This might allow you to bypass the text box and just use the listbox.

Reply | Quote

Thanks, John. I’ve pasted the SQL view into this post. I use an OR word to separate the values. More than one value can be used to return more than one account in the query. My text box would have 17133 OR 23456 in it, surrounded by quotes. It must be a text data type since an account number can contain letters.

SELECT tblCusip.Cusip, tblAccounts.AcctNo, tblAccounts.InvestorName, tblCusip.State, tblCusip.BondDescription, tblCusip.Coupon, tblCusip.Maturity, tblCusip.SPRating, tblCusip.MoodyRating, tblCusip.[Average Life], tblCusip.BondName, tblCusip.[Call day], qryTotalOwned.SumOfQty
FROM tblAccounts INNER JOIN (tblCusip INNER JOIN qryTotalOwned ON tblCusip.Cusip = qryTotalOwned.Cusip) ON tblAccounts.AcctNo = qryTotalOwned.AcctNo
WHERE (((tblAccounts.AcctNo)=[Forms]![frmChooseBondReport]![txtAccts]) AND ((tblCusip.BondQuantity)>0));

The SQL view is taken from the query, so it refers to a a form by using the Forms! type of entry. If I would use an SQL statement, I can change this. It’s just been a long day and I’m hoping to find an easy fix. I’ll do a search for other posts.

Thanks again

Reply | Quote

What you can do is to build the SQL using what’s in the text box. It all depends on where you use this query.

Alternatively you could also put the selected items in the list box (or text box if you prefer, although I’m with John on this one) in a table, then use that table in the query.

Reply | Quote

Thanks to both who replied. I’ll create a temporary table and add it to the query.

Reply | Quote

Also see post 470,621 and the link provided by MarkD.

Reply | Quote

The problem is how “OR” gets used in the sql of a query.

If I write a query to find people whose surname is Brown or Jones, the sql would look like:

select people.*
from People
where (people.surname=”Brown”) or (people.surname=”Jones”)

Note that the field name is repeated for each possible value.

Your query, using the value from the textbox looks like:

where (people.surname=”Brown” or “Jones”)

There probably is no one whose name is “Brown” or “Jones” so no people are returned.

Reply | Quote

Good point, John. That does make sense. I knew it was something practical that I was overlooking.

Reply | Quote