Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2008
    Posts
    35

    Unanswered: LIKE query over multiple fields allowed?

    Just like the thread title, my question is: Are LIKE query over multiple fields allowed? I've tried it on the DB I'm working on now, and it doesn't seem to work right. I get a blank form, no formatting or text, whenever I run the query, unless I use the wildcard, but only records with all the fields filled out show up then.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why would you be looking for the same text in more than one column?
    ..its sounds like compromised table design to me
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Post the offending SQL code from your query please.
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2008
    Posts
    35
    I apologize, I wasn't clear. I want to search for a string of text (such as a bill number in the form of xxx-xxxxxx-xx) in a total of 10 fields. The bill number can be in any one of those 10 fields.

    You don't want to see my SQL, it's a horrid mess...when I started this database in Access I had no idea that spaces and slashes in titles make for ugly SQL.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "only records with all the fields filled out show up then" sounds like a problem with nulls

    doesn't msaccess have two concatenation operators, & and +, and one of them is null-friendly?

    WHERE col1+col2+col3 LIKE '*summat*'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't understand why bill number can be in one of 10 fields... I thought it would be in a field called, I dunno, bill_number, perhaps?
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2008
    Posts
    35
    Well, to start off, my boss said to do it that way and when I started this I had barely a clue about Access.

    Second, it makes for a very bloated table, but an engineer filling out the form that writes to this table can have up to 10 changes. These changes are made in the form of adding or removing certain bills of material from a particular project.

    So if the boss wants to see all the change notices that bill 123-456789-01 is in, he can do that with ease by searching the 10 bill number fields. Make sense?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by punkn00dlez
    Make sense?
    in a pointy-haired boss kind of way, sure

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2008
    Posts
    35
    My boss pretty much asked me for a form to bloat a table with is what it boils down to. Problem is if I put 'like [Enter Bill Number]' in the criteria field in my query design for billNumber1, that's the only thing it'll search for. So if I put it in billNumber2-10 as well, it doesn't pull any replies at all. I need a way to have the query search all the billNumber fields for whatever bill needs to be found, whether is in billNumber2 on one record or billNumber 7 in another.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    See post #5 from Rudy
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by punkn00dlez
    So if I put it in billNumber2-10 as well, it doesn't pull any replies at all.
    it must be ANDing them -- you want ORs

    i can;t remember how you do that in design view

    do me a favour, build the query and put the LIKE condition into two of the fields, then switch to SQL view, and copy/paste the SQL here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2008
    Posts
    35
    I got it working now, you were right, it was ANDing them. It's working beautifully now. Would you still like to see the sql?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, that's fine, i'm happy it worked

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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