Return records based on a search of a memo field 2

[INeedAccessHelp]

Hi

Not sure if this can be done, but can anyone tell me if you can search a records memo field using "Like" and return all the records in the table that contain the parameter? I have a text box on a form where a user will type some of the information he/she is looking for in a records description. He/she will then click a button with the following code behind it:

Dim DescriptionStr As String

If IsNull(Me!Description) Then
DescriptionStr = ""
Else
DescriptionStr = "ReqDescription Like *" & Me!Description & "*"
End If

SqlStr = DescriptionStr

Me![filterFrm].Form.Filter = SqlStr
Me![filterFrm].Form.FilterOn = True


ReqDescription is the name of the field in the table. Me!Description is the text box for user input.

Any help would be GREATLY appreciated. Thanks.

-Rob

 

[Darrylles]

Hiya,

You can't search on memo fields - period.

Memo fields break the law of 'predefined' variable sizes and therefore can be virtually any size (dependant on resources).

Searching through an infinitesimal number of records with infinitesimal length strings (memos) is not the way that computers operate (that kind of situation begs for an infinitesimal search (or at least a very, very, very long search)).

Your only 'workaround' is either to pre-define keywords and force the person who creates the memo to select keywords that 'describes' their memo content before they enter it, or allow the person who is to create the memo text to add 'key words' to 1 or many text boxes that describe the memo content - then search on those.

Kind regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Steve101]

Rob,

(a) Darrylle is spot on with everything that he's said. However, if you're desperate to search within a memo field AND you're not too concerned with performance implications, then you should be able to use the LIKE operator in a query;

For example:

SELECT YourMemoFld
FROM YourTable
WHERE YourMemoFld Like "*WhateverYouLike*"

You want to be doing this sort of thing pretty selectively though; you're not getting the benefit of any indexing, unless of course you have other indexed columns in your selection criteria.

(b) There is an error in your code which could be causing you problems:

Change your line:

DescriptionStr = "ReqDescription Like *" & Me!Description & "*"

to

DescriptionStr = "ReqDescription Like '*" & Me!Description & "*'"

I have added single quotes around the Me!Description string as the filter will incorporate the 'evaluated constant'.
Hopefully this will allow your code to operate as intended.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[tlbroadbent]

"You can't search on memo fields - period."

That's news to me and to many users of Access. In addition to using the LIKE predicate, you can also search MEMO columns using the INSTR function. The follwoing example is a parameter query that will search for the string entered by the user. You can also use this technique with textbox input on a form rather than a parameter.

Use INSTR with paramter to search memo field:

SELECT MyMemos.RecID, MyMemos.MyMemo
FROM MyMemos
Where InStr([mymemo],[Enter string to find])>0;

Use LIKE with a parameter:

SELECT MyMemos.RecID, MyMemos.MyMemo
FROM MyMemos
Where [Mymemo] Like "*" & [Enter string] & "*";
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Steve101]

Yes, I was using the LIKE predicate as an example, consistent with the one used in the thread, but as Terry points out, you should be able to use any of the string operators on the memo field.


Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Darrylles]

Hi,

Sorry guys, I tried the 'search' and it worked fine.

I really thought that it was 'technically' not possible.

Don't know where I got this from except for maybe during college where the obvious 'performance issue' may have cropped up and I've dispelled it as a 'no-no'.

Is this possible through all versions? I'm on 2000.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[tlbroadbent]

Darrylle,

Performance can definitely be a issue when searching large memo fields and/or many rows. Access can't use indexes for searches as on other columns. It's just one of those issues to note and live with when this kind of search is needed. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[INeedAccessHelp]

Thanks alot guys! The performance isn't all that bad. It seems to take a little longer when your search string is found in many records.

 

[Trevoke]

This is a bit off-topic, a vocabulary point..

I always thought that 'infinitesimal' meant 'really really small'.. Am I wrong? Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam.

 

[CalibAndrew]

Trevoke,

As I understand it, "infinitesimal" doesn't necessarily mean small. It's an adjective modifier derived from the word "infinite" and I think it's a old-fashioned/fancy way to say "endlessly" You could say that something is "infinitesimally small" or "infinitesimally large". The word usually gets used to mean "really small", but you can use it with any advective, I guess.

This has been the Vocabulary Minute for July 10, 2002.

 

[tlbroadbent]

Vocabulary minute #2:

According to Webster's dictionary, infinitesimal means -[ol][li]taking on values arbitrarily close to but greater than zero
[li]immeasurably or incalculably small[/ol]

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.