Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009

    Unanswered: Query Criteria From a form field. Trying to do a contains search. Help Please.

    I have a query that has a number of fields. I only want the contains type search in one of those fields. I'm using Access 2007, building in 2003 format. I have searched for about a week for how to do this, and can't seem to figure it out.

    Here is the code which I'm using in the criteria for the query:

    Like "*" & "[Forms]![Search]![Activity]" & "*"

    I have also tried Like "*" & [Forms]![Search]![Activity] & "*"

    From my understanding, this should do the function I desire, as it should allow for the search of a partial string, instead of having to type it exactly how it is in that field.

    This field is a memo field in the parent table, as some of the entries of this field can have some length to them. I don't know if that would have anything to do with why it is not working.

    If anyone is able to help me along, I would be very grateful.

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    You can't search on Memo type fields.

    What I typically do in this type of situation is either write vba code to open a recordset and store the memo field into multiple string variables (ie. breaking it apart) and then open another recordset based upon those string variables (keeping in mind that I may need to write an OR type SQL statement to match). Or write to a temp type table importing the memo field into multiple text type fields (ie. TextField1, TextField2, TextField3 - ie. again, breaking it into parts) and then do a search on those fields, passing back the returned values.

    If you need to do searches on this field, you'll need to weigh the complexity of searching for text within that field as a memo field type versus making it a text field type that's 255 characters long and a much simplier search routine. It's kind of a give and take.
    Last edited by pkstormy; 12-02-09 at 20:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2009
    Ok, thanks for the help. It's already been decided that 255 character limit is to short for some of the information going into that field. Thank you for the input.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts