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.
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.
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.
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.
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].
please forgive my newbieness, but i guess i dont get it. do i type what your wrote in the criteria section?
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.
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.
please put all you fields name in brackets: [1name], [2name]...
YESSS that worked. Thanks so much for the help!