Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36

    Search multiple fields from one text box

    Hello,



    In by database i have a search form based on a query. What i want to know is how i can search through 3 fields in the main form with what is typed into one box.

    For example;
    My search box is called "qinternal"
    In my form i have 3 internal invoice numbers; internal1, internal2 and internal3

    Is there a way i would be able to search all 3 internal invoices by what is typed into qinternal on the search form?

    Using "Like [forms]![search]![qInternal] & "*" " in all criteria on the queries does not work. At the moment im having to use a separate box for each, which causes issues because i have to sometimes search 3 times to find a single result.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,183
    Have you put the criteria on different criteria lines in the query? Putting them on the same line creates

    WHERE 1 AND 2

    putting them on separate lines creates

    WHERE 1 OR 2

    which is what you want.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Hi Tommy,

    If I understand you correctly, you want to be able to:
    1. Enter three values - for three different Table fields - into ONE text box on a Form and then
    2. Perform the search by separating the three different values in that one text box and matching each value to a different field in the table.

    Is that correct?

  4. #4
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Quote Originally Posted by Robeen View Post
    Hi Tommy,

    If I understand you correctly, you want to be able to:
    1. Enter three values - for three different Table fields - into ONE text box on a Form and then
    2. Perform the search by separating the three different values in that one text box and matching each value to a different field in the table.

    Is that correct?

    I believe you are on the right lines yes. I have 3 fields in the table. I need one field in the query to search all 3 fields in the table to find a match.


    And thank you Pbaldy, i think i have them on the multiple criteria one already but will check.

  5. #5
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Still no luck with this one guys, any other suggestions?

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,183
    Not knowing what exactly you've tried, no. Post the SQL of the query that used what I suggested (I think Robeen is going in a different direction).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Code:
    SELECT [Job Form].[Invoice Number], [Job Form].Customer, [Job Form].[Booking Reference], [Job Form].[B/L Number], [Job Form].[Number of Containers], [Job Form].Line, [Job Form].Vessel, [Job Form].ETA, [Job Form].ETS, [Job Form].[Invoice Value ()], [Job Form].[Cost ()], [Job Form].[Payment Recieved ()], [Job Form].[Payment Made], [Job Form].[Internal invoice number], [Job Form].[Payment Made], [Job Form].IntInvoice2, [Job Form].IntInvoce3
    FROM [Job Form]
    WHERE ((([Job Form].[Invoice Number]) Like [forms]![Search]![qinvoice] & "*") AND (([Job Form].Customer) Like [forms]![search]![qcustomer] & "*") AND (([Job Form].[Booking Reference]) Like [forms]![search]![qbooking] & "*") AND (([Job Form].[B/L Number]) Like [forms]![search]![qbl] & "*") AND (([Job Form].Line) Like [forms]![search]![qline] & "*") AND (([Job Form].[Payment Made]) Like [forms]![search]![qlinepayment] & "*")) OR ((([Job Form].[Internal invoice number]) Like [forms]![search]![qInternal] & "*")) OR ((([Job Form].IntInvoice2) Like [forms]![search]![qInternal] & "*")) OR ((([Job Form].IntInvoce3) Like [forms]![search]![qInternal] & "*"));
    That is a copy of the SQL for the query. All of the invoice number text boxes in the table should be searched from the same criteria, which is entered in the text box in the search form. Thats what i want to happen anyways.

    The boxes in the table i want to search are; "internal invoice number", "intinvoice 2" and "intinvoice3"

    The search box with the criteria in is "qInternal"

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,183
    And what does "no luck" mean? My guess is it's returning the wrong records. It appears you have a parentheses problem, which personally I find easier to fix in SQL view than design view. What is the logic of what you want? If I'm reading the SQL right, you currently have

    WHERE (A And B And C And D) Or E Or F Or G

    I suspect what you want is

    WHERE A And B And C And D And (E Or F Or G)

    Parentheses are crucial when mixing And & Or, and Access throws so many in there it can be hard to figure out. I'd go into SQL mode and delete the extraneous ones, from the inside out (imagine how Access is evaluating them). In other words, these two would be the first to go:

    WHERE ((([Job Form].[Invoice Number]) Like ...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Access_Blaster is offline User
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2010
    Posts
    339
    Quote Originally Posted by tommy93 View Post
    Hello,

    In by database i have a search form based on a query. What i want to know is how i can search through 3 fields in the main form with what is typed into one box.

    For example;
    My search box is called "qinternal"
    In my form i have 3 internal invoice numbers; internal1, internal2 and internal3

    Is there a way i would be able to search all 3 internal invoices by what is typed into qinternal on the search form?

    Using "Like [forms]![search]![qInternal] & "*" " in all criteria on the queries does not work. At the moment im having to use a separate box for each, which causes issues because i have to sometimes search 3 times to find a single result.

    Thanks
    When you have 3 similar invoice numbers spread across 3 different fields, it's no wonder you are having trouble with your searches.

  10. #10
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Quote Originally Posted by pbaldy View Post
    And what does "no luck" mean? My guess is it's returning the wrong records. It appears you have a parentheses problem, which personally I find easier to fix in SQL view than design view. What is the logic of what you want? If I'm reading the SQL right, you currently have

    WHERE (A And B And C And D) Or E Or F Or G

    I suspect what you want is

    WHERE A And B And C And D And (E Or F Or G)

    Parentheses are crucial when mixing And & Or, and Access throws so many in there it can be hard to figure out. I'd go into SQL mode and delete the extraneous ones, from the inside out (imagine how Access is evaluating them). In other words, these two would be the first to go:

    WHERE ((([Job Form].[Invoice Number]) Like ...
    Ok, thankyou. I think i kind of get the picture of what you are talking about and will give this a try tonight. I will let you know how it goes.


    EDIT: Might not be useful by thought i should mention, by 'no luck' i meant the query is only returning the results if i search by the first invoice. If i search by the second or the third invoice number then there are no results returned.

    Quote Originally Posted by Access_Blaster View Post
    When you have 3 similar invoice numbers spread across 3 different fields, it's no wonder you are having trouble with your searches.
    The data input into the fields are completely different. Its needed because per job for the company i am working for there could be 3 invoices charged to us that all refer to the one invoice number for our customers.

    I.e. It is a shipping logistics company. For each customer that uses us for shipping services, there could be for example 2 companies carrying out the shipping, trucks for land based movement, and ships for across seas. This would mean 2 different invoices charged to us from them companies. Which the customer would pay us for, and we would forward the money.

  11. #11
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Code:
    WHERE [Job Form].[Invoice Number] Like forms!Search!qinvoice & "*" And [Job Form].Customer Like forms!search!qcustomer & "*" And [Job Form].[Booking Reference] Like forms!search!qbooking & "*" And [Job Form].[B/L Number] Like forms!search!qbl & "*" And [Job Form].Line Like forms!search!qline & "*" And [Job Form].[Payment Made] Like forms!search!qlinepayment & "*" And  ([Job Form].[Internal invoice number] Like forms!Search!qInternal & "*" Or [Job Form].IntInvoice2 Like forms!Search!qInternal & "*" Or [Job Form].IntInvoice3 Like forms!Search!qInternal & "*");
    This is the copy of the new SQL, just the where parts anyways. I have taken out all the brackets and now it is set out as you stated to try, yet the same is still happening?

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,183
    Can you post a db with the table and query in it we can play with, and the expected result?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Ok here is a copy of the database... had to upload it into a zip file though...

    On the search form, the box i am trying to search by is "Internal invoice". There are 3 records, with three internal invoice numbers as follows -



    record 1 AAA BBB CCC

    record 2 DDD EEE FFF

    record 3 GGG HHH III

    Searching by internal 1 finds the records as i want, but searching for number 2 or 3 doesn't unless linked to the separate boxes on the search form.

  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,183
    Once I fix the misspelled field name in the query it seems to work.
    Last edited by pbaldy; 03-02-2012 at 03:09 PM. Reason: Attachment removed to reduce board clutter
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    tommy93 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    36
    Which spelling mistake is this?

    The only one i can see is on invoice 3, although this is miss spelled in the table itself and on your screenshots? So i dont believe its that one?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Smart Search multiple fields?
    By Deisun in forum Programming
    Replies: 2
    Last Post: 08-15-2011, 12:31 PM
  2. Search across multiple fields
    By Nexus13 in forum Programming
    Replies: 2
    Last Post: 07-08-2011, 02:38 PM
  3. combo box to search multiple fields
    By jo15765 in forum Forms
    Replies: 21
    Last Post: 12-23-2010, 03:28 PM
  4. Replies: 4
    Last Post: 09-22-2010, 01:47 AM
  5. Split text field into two text fields
    By Grant in forum Access
    Replies: 6
    Last Post: 01-31-2008, 05:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums