Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text box for query criteria (2003)

    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.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Text box for query criteria (2003)

    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.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box for query criteria (2003)

    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

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Text box for query criteria (2003)

    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.

  5. #5
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box for query criteria (2003)

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

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Text box for query criteria (2003)

    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.
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box for query criteria (2003)

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text box for query criteria (2003)

    Also see <post#=470,621>post 470,621</post: > and the link provided by MarkD.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •