Case Sensitive in a Criteria with Like

[Guest]

Hi,
I have a field that included a complete citation of a publication. This
means it can have multiple authors listed,title of article and publication,
among other things.

I want to use a parameter query to let a person find all the citations by a
user definded author. I currently using a statement such as
LIKE *&[Enter author's last name]&*

The problem is if the user enters something like "Allen" they not only get
publications with the author of Allen, but publications with the word
"Challenge" in. We also cannot drop the wildcard characters becasue the
author name could be listed as "Allen, C." or "C. Allen" depending on where
in author list the name is located.

Is there a way to force a case sensitve search in the LIKE statement?

 

[Jim/Chris]

Even if you would go case sensitive you would still get
results like allentown. Why not keep the authors in a
seperate field if possible.

Jim

 

[Guest]

We bought into a Ecommerce package that is forcing us to go with one field
for the "details." I agree separate fields would have been the way to go.

Even if you would go case sensitive you would still get
results like allentown. Why not keep the authors in a
seperate field if possible.

Jim

-----Original Message-----
Hi,
I have a field that included a complete citation of a publication. This
means it can have multiple authors listed,title of article and publication,
among other things.

I want to use a parameter query to let a person find all the citations by a
user definded author. I currently using a statement such as
LIKE *&[Enter author's last name]&*

The problem is if the user enters something like "Allen" they not only get
publications with the author of Allen, but publications with the word
"Challenge" in. We also cannot drop the wildcard characters becasue the
author name could be listed as "Allen, C." or "C. Allen" depending on where
in author list the name is located.

Is there a way to force a case sensitve search in the LIKE statement?

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln
.

 

[John Vinson]

Is there a way to force a case sensitve search in the LIKE statement?

Not easily. The InStr() function has an optional argument which will
force a binary comparison; you could use a criterion of

Where InStr([memofield], "Allen", 0) > 0

This will of course be slow both because of non-indexing and because
you'll be passing the entire memo field to a function for each row.

John W. Vinson[MVP]