LIKE or CONTAINS not giving me what i need...Help!

[blankbandit]

Hello everyone,
Problem: I have an MS Access database in Frontpage that I'm using for my online site. Table is set up w/ a field named, "Artist" (amongst others). In this field I have artist's last name followed by a comma, a space, then the artist's first name. I am using the CONTAINS operator for my sql query statment. Everything works fine on the site if you search only by the first name, last name, or by typing in, exactly, the artist last name, comma, space, then the first name i.e. Presley, Elvis. However, if one were to type in "Elvis Presley", no records return. Why is this? I have also tried the LIKE operator and I get the same results. Here is the code where 45s is my table name and the others are just additional fields of which i have no problems:

SELECT * FROM 45s WHERE (Artist LIKE '%::Artist::%' OR Title LIKE '%::Title::%' OR Record_Label LIKE '%::Record_Label::%') ORDER BY Artist ASC

I'd appreciate any help as I've failed to get a solution from other forums and computer scientists that I've asked.

Thanks!
David

 

[Pat Hartman]

Jet syntax uses * rather than % and I have no idea what the ::'s are. You might have to post the question to a Front Page site since the syntax you are using is not standard VBA.

The reason that Elvis Presley isn't found is because people are smarter than computers and can recognize when something is logically reordered. There is no string equal to "Elvis Presley" in the database. The string you want to match is "Presley, Elvis" - two completely different animals. You can find "Elvis" or "Presley" because both of those exist within the actual string. "Elvis Presley" does not exist within "Presley, Elvis".

Your real problem is improper table design. It is poor practice to store multiple attributes in a single field and you are seeing one of the results of that - difficulty in searching. There are lots more.