Fuzzy search using sql

[amitbadgi]

Hi guys, I have created a form in access which is used to search a
access database. There are 2 cities and each of em has 2 access tables,
I have successfully written the sql query and attached it to a macro
which in turn attached it to the search button on my form. Now my
question is that I want to enable teh fuzzy search for my form, which
is , if a user enters a Address, say 1547, he shld be able to get all
the following matches from the database
1547 bulldog
1547 wshaw
1547 cedar
and so on....
this is the query that I have written that takes in user input and
displays the result. Currently I have to enter the exact address etc,
else it wouldnt work and I want teh fuzzy search to work.

SELECT OneWorld.bus_id, OneWorld.bus_nam, OneWorld.bus_add,
OneWorld.city, OneWorld.state, OneWorld.zip, OneWorld.phone,
OneWorld.license
FROM [Select Distinct dbo_businessNC.bus_id as
bus_id,dbo_businessNC.bus_name as bus_nam,dbo_businessNC.adrs1 as
bus_add,dbo_businessNC.city as city,dbo_businessNC.state as
state,dbo_businessNC.zip as zip, dbo_businessNC.phone as phone,
dbo_occhistoryNC.license as license from dbo_businessNC,
dbo_occhistoryNC WHERE ( dbo_businessNC.bus_id=dbo_occhistoryNC.bus_id)
UNION ALL Select bus_id, bus_name as bus_nam, bus_add, city, state,
zip, phone, license from Hawaccsumm
]. AS OneWorld
WHERE (((OneWorld.bus_id) = [Forms].[Form1].[Text2])) OR
(((OneWorld.bus_nam) =[Forms].[Form1].[Text5])) OR
(((OneWorld.bus_add)= [Forms].[Form1].[Text8])) OR (((OneWorld.phone)
=[Forms].[Form1].[Text10])) OR
(((OneWorld.zip)=[Forms].[Form1].[Text15]));


Thanks in advance

 

[Rick B]

to do a search like you emntion, you need to use "like"

Like "*" & [Enter Partial Address] & "*"

Is how you do it in design view.

 

[amitbadgi]

Thanks for the reply Rick, but I need this to work when a user inputs
in the form. And I tried doign this,
Like "*" &[Forms].[Form1].[Text2]& "*"
Text2 is the name of teh text box on teh form which is used by teh user
to enter values in Form1.

But it isnt working. After defining the above, I entered a BID and it
doesnt show any result.
Thanks in advance.

 

[amitbadgi]

HEy Rick, sorry bout the previous reply, its working just fine. Thanks
a lot.

 

[amitbadgi]

Hey rick, its working fine for one entry but when i define it this way
WHERE (((OneWorld.bus_id) LIKE "*" &[Forms].[Form1].[Text2]& "*")) OR
(((OneWorld.bus_nam) LIKE "*" &[Forms].[Form1].[Text5]& "*"))....so on

It isnt working, its giving all the records as out put, text5 is the
input for teh business name in the form. Thanks

 

[Rick B]

Shouldn't you be doing an "AND" to find records where both conditions are
true?

Doing "OR" will find everycase where either condition is true.

Also, leaving a blank in one of those prompts will now pull ALL records.

"*" & blank & "*" pulls everything.

 

[amitbadgi]

Thanks Rick, but I am using OR because, teh user can either enter a BID
which is TEXT2 or enter business name which is TEXT5, and 3 other
options, hence when he eneters either one of teh options the related
data is pulled out. Now when i defined teh query as u had suggested

WHERE (((OneWorld.bus_id) LIKE "*" &[Forms].[Form1].[Text2]& "*")) OR
(((OneWorld.bus_nam) LIKE "*" &[Forms].[Form1].[Text5]& "*"))....so on

Now since I leave say BID blanka nd type in a business name it pulls in
all records, so how do i write teh query so that when a user eneters
any one option teh related data is pulled out. Thanks

Shouldn't you be doing an "AND" to find records where both conditions are
true?

Doing "OR" will find everycase where either condition is true.

Also, leaving a blank in one of those prompts will now pull ALL records.

"*" & blank & "*" pulls everything.

--
Rick B

Hey rick, its working fine for one entry but when i define it this way
WHERE (((OneWorld.bus_id) LIKE "*" &[Forms].[Form1].[Text2]& "*")) OR
(((OneWorld.bus_nam) LIKE "*" &[Forms].[Form1].[Text5]& "*"))....so on

It isnt working, its giving all the records as out put, text5 is the
input for teh business name in the form. Thanks

 

[Rick B]

I'm pretty sure you need to change it to ANDS.

Try it.

Any BLANK field will pull everything, then any field with an entry will
scale down the results.



--
Rick B

Thanks Rick, but I am using OR because, teh user can either enter a BID
which is TEXT2 or enter business name which is TEXT5, and 3 other
options, hence when he eneters either one of teh options the related
data is pulled out. Now when i defined teh query as u had suggested

WHERE (((OneWorld.bus_id) LIKE "*" &[Forms].[Form1].[Text2]& "*")) OR
(((OneWorld.bus_nam) LIKE "*" &[Forms].[Form1].[Text5]& "*"))....so on

Now since I leave say BID blanka nd type in a business name it pulls in
all records, so how do i write teh query so that when a user eneters
any one option teh related data is pulled out. Thanks

Shouldn't you be doing an "AND" to find records where both conditions are
true?

Doing "OR" will find everycase where either condition is true.

Also, leaving a blank in one of those prompts will now pull ALL records.

"*" & blank & "*" pulls everything.

--
Rick B

Hey rick, its working fine for one entry but when i define it this way
WHERE (((OneWorld.bus_id) LIKE "*" &[Forms].[Form1].[Text2]& "*")) OR
(((OneWorld.bus_nam) LIKE "*" &[Forms].[Form1].[Text5]& "*"))....so on

It isnt working, its giving all the records as out put, text5 is the
input for teh business name in the form. Thanks

 

[amitbadgi]

Thanks Rick, its workign perfectly fine. Thanks again.

I'm pretty sure you need to change it to ANDS.

Try it.

Any BLANK field will pull everything, then any field with an entry will
scale down the results.



--
Rick B

Thanks Rick, but I am using OR because, teh user can either enter a BID
which is TEXT2 or enter business name which is TEXT5, and 3 other
options, hence when he eneters either one of teh options the related
data is pulled out. Now when i defined teh query as u had suggested

WHERE (((OneWorld.bus_id) LIKE "*" &[Forms].[Form1].[Text2]& "*")) OR
(((OneWorld.bus_nam) LIKE "*" &[Forms].[Form1].[Text5]& "*"))....so on

Now since I leave say BID blanka nd type in a business name it pulls in
all records, so how do i write teh query so that when a user eneters
any one option teh related data is pulled out. Thanks

Shouldn't you be doing an "AND" to find records where both conditions are
true?

Doing "OR" will find everycase where either condition is true.

Also, leaving a blank in one of those prompts will now pull ALL records.

"*" & blank & "*" pulls everything.

--
Rick B



Hey rick, its working fine for one entry but when i define it this way
WHERE (((OneWorld.bus_id) LIKE "*" &[Forms].[Form1].[Text2]& "*")) OR
(((OneWorld.bus_nam) LIKE "*" &[Forms].[Form1].[Text5]& "*"))....so on

It isnt working, its giving all the records as out put, text5 is the
input for teh business name in the form. Thanks

 

[Dale Fye]

Are you building this SQL string in code, or are you trying to build it in a
query and save it. Whenever I want to use this type of conditonal logic, I
build the SQL in code behind a command button, then populate the SQL property
of the QueryDef, then run the requery the object (form or control) that is
using the query.
Something like below. Another way to handle this, if you only want them to
search by a single field at a time, is to create a combo box and fill it with
the names of the fields you want the user to be able to seach by. Then, they
select the field, type the value into the search text box and you use the
same logic as below, but now you use the value in the combo box to designate
the field to be searched.

Dim strSQL as string
Dim varCriteria as variant

strSQL = "SELECT * FROM myTable"

varCriteria = NULL
If LEN(me.txt_FilterLastName & ") > 0 then
varCriteria = "([LastName] = " & chr$(34) & me.txt_FilterLastName &
chr$(34) & ")"
Endif

If Len(me.txt_FilterFirstName & "") > 0 then
varCriteria = (varCriteria + " AND ") _
& "([FirstName] = " & chr$(34) & me.txt_FilterFirstName
& chr$(34) & ")"
endif

strSQL = strSQL & (" WHERE " + varCriteria)

me.cbo_yourCombo.rowsource = ""
Currentdb.querydef("yourQuery").SQL = strSQL
me.cbo_yourCombo.rowsource = "yourQuery"

HTH
Dale

***************