Problem filtering memo field on subform

[Les Isaacs]

Hi All

This works fine:

[Forms]![qry x main table subform master]![MainDetails].Form.Filter = "[paytyp] like '*" & FilterString & "*' "

but this does not (i.e. the filter returns no records):

[Forms]![qry x main table subform master]![MainDetails].Form.Filter = "[pay memo] like '*" & FilterString & "*' "

The only difference is that [paytyp] is a text field and [pay memo] is a memo field.

I assume memo fields are more fussy about delimeters, or something ...
Does anyone know what I need to do

Thanks for any help.
Les

 

[Rx_]

Access can only evalutate at the first 254 characters if the the left$() command is used.

Left$([memo],254) could provide a searchable string. instr(left$([memo],254),"~~~)>0 will tell you if any specific string
contains ~~~

Memo fields are a different animal. I once put in photos of students in a memo field and designed a sort order for Pretty --> Ugly
And a memo field for websites hyperlinks sorted from Useful --> worthless Actually that is a joke to emphasis how memo fields are not like text at all.

What would worry me is how much processing, disk time, and network bandwidth could be consumed using the Left and Instr.
My database uses a memo field for "notes" At the time a user enters or updates the notes, there is a search for the top 20 key words. These are put into a text field column. This provides a speed search of the 300,000 records rather than trying to convert and qualify each record with out an index. As you might guess, words such as A, An, The, Of, Or, In, Thanks, Useful, Appreciate .... are not key words that anyone would ever need to search on. (pun intended).

 

[Les Isaacs]

Hello Rx_
Thanks very much for your reply. Limiting the searched value to the left 25 characters worked! Not entirely elegant but at least I get the correct result - for now! I can see memos are perhaps to be avoided where possible - so will do that in future.
Thanks again
Les