Searching in Microsoft Access

Q: I imported an Excel spreadsheet into Access 2003 Pro. The spreadsheet had the main row with titles that Access used as the field names. The first column is named Movies. One title in this column is The Santa Clause, and others contain the word "Santa." If I create a query with the criteria Santa in the Movies field it returns nothing at all. But if I type the whole name, e.g. The Santa Clause, it finds that movie. I would like to find all movies whose titles contain "Santa." I can't find the way. - Carlos Kruger.





A: The key point here is that an Access query is not the same as a search in Excel or Word. If you just type a value in the Criteria row it finds records that exactly match that value. You need to use a slightly different syntax to get the search you're after. Here are some examples.

Like "*Santa*" contains the word Santa (or santa)
Not "*Santa*" does not contain the word Santa.
Like "Santa*" begins with Santa
Like "S???a"a 5-letter word that starts with S and ends with a
Is Nullthe field is null
"" (just two quotes) not null but contains an empty string.
> "Santa"values that come after Santa alphabetically
"Santa" OR "Claus" equals one of those two values
IN("Curly", "Larry", "Moe") equals any of the listed values

As you can see, there are many possibilities!Neil J. Rubenking.