Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter vs Query on a Memo Field (2000)

    I'm searching for a text string in a memo field using wildcard characters.

    *332740*

    I put this criteria in using Filter by Form on the table. The records it brings back does not contain this string. it contains "...033274" When I do a Find on the resulting records from the filter for 332740 it's not found.

    But if I use a query and put the criteria of the memo field as *332740*, no records are found.

    So what's the difference in using the filter versus a query on a memo field?

    thanks.
    Sarah

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Filter vs Query on a Memo Field (2000)

    Using a filter does some things behind the scene based on whether the field is a text field or a numeric field. Is the field being tested a numeric string or is it a number?
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter vs Query on a Memo Field (2000)

    a numeric string...

    i think so... The numbers are in a memo field and I'm entering the criteria for the filter exactly like this: *332740*

    sarah

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Filter vs Query on a Memo Field (2000)

    <P ID="edit" class=small>(Edited by WendellB on 17-May-04 06:45. )</P>If the field is a numeric string, then your criteria for a query should be:
    <font color=blue><font face="Georgia">Like "*332740*"</font face=georgia></font color=blue>
    Try that and see if you can get the record in question to be returned.

    <font color=red>Oops - I just realized you are dealing with a memo field!</font color=red> They can do weird things if their length is greater than 255 characters. There are a number of constraints in doing filters and queries on memo fields, and in some situations you may find the result being truncated to 255 characters, which may explain some of the things you are seeing. I also believe there were some fixes for problems of this sort in the latest service pack for Office 2000.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter vs Query on a Memo Field (2000)

    the search is working now...

    not sure what changed. now when I search for *332740* no records are returned, which is correct.

    I did a search (using a filter) for a similar string in the memo field, *33270* and it returned one record. In the resulting record the memo field had over 255 characters. So then I clicked in the memo field, then clicked the Find button, typed in the 33270, set the Look In to the name of the field and the Match to Any Part of the Field, then I clicked Find Next. The string wasn't found. So I changed the Look In to the name of the form, clicked Find Next, and the string was found (not hightlighted but found).

    Do you think this is because it's a memo field with over 255 characters? Does the Find function only work with the first 255 characters?

    Sarah

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Filter vs Query on a Memo Field (2000)

    I think the Find actually looks at the entire memo field, but anything that involves any formatting or sorting often looks only at the first 255 characters. I believe there was also a fix for some problems related in memo fields in one of the Jet DLL service packs - if you aren't on the current version (SP8) you might want to try applying it.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter vs Query on a Memo Field (2000)

    I do have Jet SP8 installed. It was bundled with the latest fixes for the Spasser worm.

    By sorting you mean ascending or descending order on the memo field? I make sure I do a remove filter/sort before doing another sort. And as for formatting? What sort of formatting are you referring to? On the memo field?

    Sarah

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Filter vs Query on a Memo Field (2000)

    Basically, any sort is applied only to the first 255 characters. In addition, any use of the Format() functions, or any of the Aggregate functions such as DCount, DMax, etc, or any query that uses the GroupBy SQL clause can cause weird behavior. If this really gets to be a sticky issue for you, you might consider switching to SQL Server or the MSDE. They support text fields up to 8000 bytes rather than the 255 that Access limits you to. I should point out that if you use ODBC linked SQL Server tables, Access still things such fields are memo fields and the sorting and such may still be only on the first 255 characters.
    Wendell

  9. #9
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter vs Query on a Memo Field (2000)

    Thanks for the info and help.

    Sarah

Posting Permissions

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