Search form for memo field

[sharpnote]

Hi! I'm developing a program to search several fields. One of which is a memo field with large characters. Using wildcard in the search form I made, it only returns the record if the first word of the field is typed in the search box. I want to type any word in any part of the field to return the record.

This is my code

' Check for LIKE Subject
If Me.txtsubject > "" Then
varWhere = varWhere & "[subject] LIKE """ & Me.txtsubject & "*"" AND "
End If

subject is a memo field.

 

[pr2-eugin]

Hello sharpnote, Welcome to AWF..

Are you sure that you need to do this? Using LIKE operator is not highly recommended, as it will not make use of any indexes and search might be slower.. Specially considering the case of searching a Memo field..

If you do want to go ahead with this expensive search.. you need to surround the txtsubject with wildcard characters..

varWhere = varWhere & "[subject] LIKE ""[COLOR=Red][B]*[/B][/COLOR]" & Me.txtsubject & "*"" AND "

 

[sharpnote]

Hi pr2-eugin,

Thanks for the reply. Actually, I'm not so familiar with vba codes. I downloaded a free code for dynamic search box. I know the codes are for text field data type, that's why I emphasize in my question that this field is a memo type. Could you please give me a code appropriate for a memo field that can be used in that search box program. Thanks agai

 

[sharpnote]

Hello sharpnote, Welcome to AWF..

Are you sure that you need to do this? Using LIKE operator is not highly recommended, as it will not make use of any indexes and search might be slower.. Specially considering the case of searching a Memo field..

If you do want to go ahead with this expensive search.. you need to surround the txtsubject with wildcard characters..

varWhere = varWhere & "[subject] LIKE ""[COLOR=Red][B]*[/B][/COLOR]" & Me.txtsubject & "*"" AND "

I tried it and it's working for the meantime having only 49 records. I'll be damned! just a minor placement of "*" made a lot of difference and big result.

I know this will be problematic once the database becomes big. If you can give me the correct code for the memo, much appreciate. Thanks again.

 

[pr2-eugin]

sharpnote, did you try the above? Memo fields are similar to Text, except for the fact they store a very large number of characters..

 

[sharpnote]

sharpnote, did you try the above? Memo fields are similar to Text, except for the fact they store a very large number of characters..

Yes pr2-eugin. I did and so far it's working, I'll be putting more data and let's see how far will it go.