Partial v. exact match 1

[emozley]

I have an access database that contains information about books - title, author publisher.

I am trying to make a search form that allows someone to choose whether they want to match the whole word or go for a partial match. A partial match is easy:

SELECT Author FROM Books WHERE Author LIKE '%Adam%'

Let's say this returns two records:

1. Douglas Adams
2. Adam Smith

How can I modify the SQL so that if the user specifies match whole word on the form and they search for 'Adam' it will ignore 'Douglas Adams' and only show 'Adam Smith'.

I am wary of putting spaces on either side of the search term because the name might appear at the start, middle or end.

eg SELECT Author FROM Books WHERE Author LIKE '% Adam %' wouldn't work because there is no space before 'Adam Smith'.

Is it possible to do something like

SELECT Author FROM Books WHERE " " & Author & " " LIKE '% Adam %'

or would this either not work at all or sometimes miss results?

Thanks very much!

Ed

 

[groleau]

I first chose "Do an iif(x is null,"", function(x)) "

But calling a VBA function four times per record,
with 800,000 records, was of course VERY slow.

Solution two:
1. SELECT DISTINCT X .... (a few seconds)
2. SELECT X, iif(x is null,"", function(x)), .... FROM (1) (a few more seconds)
3. Do a JOIN on X selecting the function results (a few more seconds)

--
Wes Groleau

http://www.Parkview.com/

 

[PHV]

Your function should deal with null values:
Public Function SVC_01_01(SVC01 As Variant) As String
If IsNull(SVC01) Then
SVC_01_01 = ""
Exit Function
End If
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[groleau]

iif(x is null,"", function .....

deals with null values.

--
Wes Groleau

http://www.Parkview.com/

 

[PHV]

I just posted to show you how a function may deal with Null.

 

[groleau]

to show you how a function may deal with Null"

Oh, OK. Thanks. I had a difficult time coming up with that a few weeks ago in another context. I think my help files are not installed correctly. I thought it kind of
"lame" that "is nothing" in VBA wouldn't be true for a
null brought into a recordset.

--
Wes Groleau

http://www.Parkview.com/