Using AND and OR in wildcard Parameter queries

  • Thread starter Thread starter Craig Evans
  • Start date Start date
C

Craig Evans

I know this should be simple, but I'm having trouble with it...

I have a very basic table that has a field showing authors and book
titles in the same field, in the following format:

"author name - title"

The information has been imported from a text file, and is all in one
field.

I'm trying to build a parameter query that will allow me to enter both
an author and a title. The problem comes in that the author names have
often been entered in various ways - for example:

Postman
Neil Postman
N. Postman
Postman, Neil
etc

Also, a user may not know the entire title of a book. So, what I need
is a parameter query that will let me use wildcards for both the
Author Name, and the Title, as well as the AND or OR operators. I've
tried the following criteria:

Like "*" & [Enter name of Author] & "*" AND
Like "*" & [Enter title] & "*"

But it's not working right. Is it possible to use the AND operator in
this manner, with wildcards on both sides, or should I be doing this
another way?

Eventually I will be trying to split the Authors and titles into
separate fields, which will simplify things, but there are about 25000
records, and the format is not always consistent (sometimes they've
got the title first, then the author, etc!), so it may take awhile.

Any help is appreciated!

E
 
Your query criteria should be:
Like [Enter name of Author] AND Like [Enter title]
This will enable the user to use wildcards:
*postman* for the author for example.

This doesn't allow users to enter the AND/OR parameters. I am not even sure
you can do this through parameter queries. I have done it programmatically
with forms and VBA.

Sal
www.cedrostec.com
 
Craig

If the field sometimes is forwards and sometimes backwards, how would Access
know which part is the title and which is the author?

If I were faced with this assignment, the first thing I'd do is parse those
"multi-fact, one field" values into separate fields.

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top