Fuzzy searches

[Guest]

I've been away from Access for several years, I am trying to create a seach
on a field that is a memo. I have 246 memo's in the field and want to create
someway to enter a key word and have it return the memo's with that word in
it. Any ideas? I remember that I did this years ago in Access 97. Thanks

 

[Ken Snell \(MVP\)]

SELECT *
FROM TableName
WHERE FieldName Like "*" & [Enter string:] & "*";

 

[Alessandro Baraldi]

Slick ha scritto:

I've been away from Access for several years, I am trying to create a seach
on a field that is a memo. I have 246 memo's in the field and want to create
someway to enter a key word and have it return the memo's with that word in
it. Any ideas? I remember that I did this years ago in Access 97. Thanks

Sorry about my english... i don't undertand if you have 246 Memo
fields..?

So you need to search a word in each Memo fields...?

Right so...?

If this is exact, it's strange situation, but you can use the code
suggest you by Ken Snell
inside a Loop.

Dim mdb as DAO.Database
Dim mfld as DAO.Fields
Dim mrs as DAO.Recordset
Dim rsS as DAO.Recordset
Dim sSQL as string
Dim Word2Find as string
Word2Find =InputBox("Search...")
set mdb=Currentdb()
Set mrs=mdb.OpenRecordset("SELECT * FROM T1 WHERE 1=0", dbReadOnly)
For each mfld in mrs.Fields
If mfld.Type=dbMemo then
sSQL="SELECT * FROM T1 " & _
"WHERE [" & fld.name & "] Like "*'" & Word2Find &
"'*"

Set rsS=mdb.OpenRecordset(sSQL,dbReadOnly)
if not rsS.EOF then
' Now i don't know how you need to use this....!
Msgbox "FIND IT on " & mfld.name
exit for
End if
set rsS=Nothing
End if
Next

This is very strange......

@Alex

 

[Guest]

Ken, thanks, but I guess I'm rustier than I though, where do I enter this?
Here are some specifics...My access database is named Prideworks...my table
name is PotteryTable and the field is Memo. I want to create a query to
return the infomation. This is the only field I'm dropping into the query
grid.
--
Slick

SELECT *
FROM TableName
WHERE FieldName Like "*" & [Enter string:] & "*";

--

Ken Snell
<MS ACCESS MVP>

I've been away from Access for several years, I am trying to create a
seach
on a field that is a memo. I have 246 memo's in the field and want to
create
someway to enter a key word and have it return the memo's with that word
in
it. Any ideas? I remember that I did this years ago in Access 97. Thanks

 

[Guest]

Ken, it worked, thanks, I was forgetting to enter the complete fieldname in
the WHERE statement. Thanks again!
--
Slick

SELECT *
FROM TableName
WHERE FieldName Like "*" & [Enter string:] & "*";

--

Ken Snell
<MS ACCESS MVP>

I've been away from Access for several years, I am trying to create a
seach
on a field that is a memo. I have 246 memo's in the field and want to
create
someway to enter a key word and have it return the memo's with that word
in
it. Any ideas? I remember that I did this years ago in Access 97. Thanks

 

[Guest]

Alessandro, thank you for replying. Ken gave me the correct response. But,
so as to not confuse you from my earlier msg...I had a field I created in my
table named Memo. I put this field into a form so others could add a memo to
the piece of pottery, thats why there was 246 memos already in the table
under the Memo section. Thanks again!
--
Slick

Slick ha scritto:

I've been away from Access for several years, I am trying to create a seach
on a field that is a memo. I have 246 memo's in the field and want to create
someway to enter a key word and have it return the memo's with that word in
it. Any ideas? I remember that I did this years ago in Access 97. Thanks

Sorry about my english... i don't undertand if you have 246 Memo
fields..?

So you need to search a word in each Memo fields...?

Right so...?

If this is exact, it's strange situation, but you can use the code
suggest you by Ken Snell
inside a Loop.

Dim mdb as DAO.Database
Dim mfld as DAO.Fields
Dim mrs as DAO.Recordset
Dim rsS as DAO.Recordset
Dim sSQL as string
Dim Word2Find as string
Word2Find =InputBox("Search...")
set mdb=Currentdb()
Set mrs=mdb.OpenRecordset("SELECT * FROM T1 WHERE 1=0", dbReadOnly)
For each mfld in mrs.Fields
If mfld.Type=dbMemo then
sSQL="SELECT * FROM T1 " & _
"WHERE [" & fld.name & "] Like "*'" & Word2Find &
"'*"

Set rsS=mdb.OpenRecordset(sSQL,dbReadOnly)
if not rsS.EOF then
' Now i don't know how you need to use this....!
Msgbox "FIND IT on " & mfld.name
exit for
End if
set rsS=Nothing
End if
Next

This is very strange......

@Alex