SQL not returning desired data

P

Penny

Hi all,

I'm currently using the following query string. Artist names are kept in the
database in the "Dylan, Bob" format. A search for "Bob Dylan" or "Dylan"
will return no result at all, where as "Bob" will return Bob Dylan and all
other Bob's. Is there a way I can adjust the query string to allow more
intuitive search results?

"SELECT tblArtists.ArtistName FROM tblArtists WHERE tblArtists.ArtistName
Like'%strArtistName%' ORDER BY tblArtists.ArtistName"

Any ideas?

Regards,

Penny.
 
R

RB Smissaert

Try something like this:

dim strArtistName2 as string

strArtistName2 = Mid(str, InStr(1, strArtistName, " ", vbBinaryCompare) + 1)
& _
", " & _
Left(strArtistName, InStr(1, strArtistName, " ", vbBinaryCompare) - 1)

"SELECT tblArtists.ArtistName FROM tblArtists WHERE
tblArtists.ArtistName Like '%strArtistName%' OR
tblArtists.ArtistName Like 'strArtistName%' OR
tblArtists.ArtistName Like '%strArtistName' OR
tblArtists.ArtistName = 'strArtistName2'
ORDER BY tblArtists.ArtistName"

You will have to work this out further.

RBS
 
T

Tom Wickerath

Hi Penny,
Artist names are kept in the database in the "Dylan, Bob" format.
Click to expand...
Is there any particular reason for doing this? Why not store last names in
an ArtistLastName field and first names in an ArtistFirstName field. That
would greatly simplify your searches.

Tom
_______________________________________

:

Hi all,

I'm currently using the following query string. Artist names are kept in the
database in the "Dylan, Bob" format. A search for "Bob Dylan" or "Dylan"
will return no result at all, where as "Bob" will return Bob Dylan and all
other Bob's. Is there a way I can adjust the query string to allow more
intuitive search results?

"SELECT tblArtists.ArtistName FROM tblArtists WHERE tblArtists.ArtistName
Like'%strArtistName%' ORDER BY tblArtists.ArtistName"

Any ideas?

Regards,

Penny.
 
D

Douglas J. Steele

If the name's stored as "Dylan, Bob", searching for "Bob Dylan" will
definitely not generate a hit. However, searching for Dylan should. Are you
sure that no extra spaces are being typed?

I assume that what you're showing isn't actually what you've got in your
application. You must have something like

strSQL = "SELECT tblArtists.ArtistName FROM tblArtists WHERE
tblArtists.ArtistName
Like'%" & strArtistName & "%' ORDER BY tblArtists.ArtistName"

If there is a chance of extra spaces, try the following instead:

strSQL = "SELECT tblArtists.ArtistName FROM tblArtists WHERE
tblArtists.ArtistName
Like'%" & Trim$(strArtistName) & "%' ORDER BY tblArtists.ArtistName"
 
P

Penny

Thanks for your suggestions guys.

I'll take some time out to digest them.

Regards,

Penny
 
T

Tom Wickerath

If you decide to use separate fields to store the names, and you have
hundreds of names to deal with, you might want to take a look at a utility
called Splitter for Microsoft Access:

http://www.infoplan.com.au/splitter/

Tom
_____________________________________

:

Thanks for your suggestions guys.

I'll take some time out to digest them.

Regards,

Penny
 
P

Penny

Tom,

Thanks for your suggestion about splitting the names but what if the artist
name is Henry Bob Canola and the Canadian Harvest Symphony Metal Heads? How
many name fields would I need to cover all possibilities?

Regards,

Penny.
 
T

Tom Wickerath

Hi Penny,
...but what if the artist name is Henry Bob Canola and the Canadian Harvest Symphony Metal Heads?
Click to expand...

This sounds more like an album name rather than an artist name. You likely
have a many-to-many (M:N) relationship between artists and albums, ie.

An artist can be involved with many albums and
An album can include many artist.

This would require a third linking (aka. join, intersection) table. This
way, each artist name would only need to be stored in the database one time,
in a tblArtist table. Likewise, each album (or CD) name would only need to be
stored one time, in order to relate it to many artist. In this case, I would
store the entire album name in a tblAlbums table, including the name of the
artist, since it is apparently a part of the album name. A design can even be
made that allows associating particular artist with various songs on the
album, if you care to take it that far.

As to whether you choose to store Henry Bob in two fields (ArtistFirstName
and ArtistMiddleName) or just one field (ArtistFirstName) is a design
desision that you'll need to make.

Hope this helps.

Tom
_________________________________________

:

Tom,

Thanks for your suggestion about splitting the names but what if the artist
name is Henry Bob Canola and the Canadian Harvest Symphony Metal Heads? How
many name fields would I need to cover all possibilities?

Regards,

Penny.
 
D

Douglas J. Steele

While I haven't heard of that particular band, it doesn't sound improbable
to me that it's an artist, not an album. It's conceptually no different than
"Paul Revere and the Raiders"

Since there are no set rules for Artist names (how would you store Cher?),
I'm not sure multi-part name fields are necessarily appropriate.

What I do is store the name of the artist as a single field, plus also store
a sort field (so that Led Zeppelin is sorted as Led Zeppelin, while Elton
John is sorted as John, Elton). I have no idea how you'd sort Henry Bob
Canola and the Canadian Harvest Symphony Metal Heads though! <g>
 
Top