Results 1 to 9 of 9
  1. #1
    bkvisler is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    22

    Find specific value in any field

    Is it possible to run a query that will show all records that that have a certain value that may be in one of mutiple fields? For example I want to be able to see all records that have "xyz" in any field on the data sheet.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    select * from tbl where field1 like "*xyz*" or field2 like "*xyz*" or field3 like "*xyz*" or field4 like "*xyz*" or field5 like "*xyz*" or field6 like "*xyz*" or field7 like "*xyz*"

    if you don't want to list all the field names, or you want to fit in different table, you can program a procedure to do it.

  3. #3
    bkvisler is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    22
    I think I understand that, but I want to run a parameter query. For example, I enter a name and access finds all records where that name shows up in any field.

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    replace the xyz with a name:
    select * from tbl where field1 like "*"& [Input Name] & "*" or field2 like "*"& [Input Name] & "*" or field3 like "*"& [Input Name] & "*" or field4 like "*"& [Input Name] & "*" or field5 like "*"& [Input Name] & "*" or field6 like "*"& [Input Name] & "*" or field7 like "*"& [Input Name] & "*"

    when you open the query, Access will prompt for [Input name].

  5. #5
    bkvisler is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    22
    please forgive my newbieness, but i guess i dont get it. do i type what your wrote in the criteria section?

  6. #6
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    When you design the query, change from "Design View" to "SQL View" by choosing from menu "View" -> "SQL view",
    copy my text "select..." and paste to SQL view,
    change "field1","field2"... to you field names of the table,
    change "tbl" to your table name.

    then run the query. Or you can then change back to "Design view" to check what it looks like.

  7. #7
    bkvisler is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    22
    Ok... I typed

    select * from Master Record where 1Name like "*"& Name] & "*" or 2Name like "*"& [Name] & "*" or 3Name like "*"& [Name] & "*" or 4Name like "*"& [Name] & "*" or 5Name like "*"& [Name] & "*" or 6Name like "*"& [Name] & "*" or 7Name like "*"& [Name] & "*" or 8Name like "*"& [Name] & "*" or 9Name like "*"& [Name] & "*" or 10Name like "*"& [Name] & "*"

    and got the error
    Syntax error (missing operator) in query expression.

    did i make a mistake??
    thanks so much for all the help.

  8. #8
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    please put all you fields name in brackets: [1name], [2name]...

  9. #9
    bkvisler is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    22
    YESSS that worked. Thanks so much for the help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 07-16-2010, 09:06 AM
  2. Hyperlink to a specific Field/Cell?
    By tbutters in forum Database Design
    Replies: 8
    Last Post: 06-04-2010, 12:27 PM
  3. Replies: 15
    Last Post: 02-16-2010, 10:58 AM
  4. Replies: 1
    Last Post: 11-10-2009, 03:20 PM
  5. find specific data
    By hoachen in forum Queries
    Replies: 3
    Last Post: 08-19-2009, 08:54 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