Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sorting a memo field? (a2002)

    I'd like to be able to sort memo fields. I thought the way to go would be to add a text field in addition to the memo field in the record. Then you could enter key words or topics in the text field based on the contents of the memo field and sort on the text field.

    However, in order to create a continuous form displaying the memo fields I needed a multitable table query that allowed me to tie a survey question response to the survey, the survey question, and the respondent. That query is not updateable so I can't enter the key words in the survey requestion response table after reviewing the contents of the memo fields.

    Is there a better strategy for sorting a memo field or a work around for updating a field in a table that's part of a multitable query? I'm sure there's a simple solution but I'm not seeing it.

    Eli

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sorting a memo field? (a2002)

    Elizabeth,

    For a multi-table to be updateable, there must be a unique index (preferably the primary key) on the field on the "one" side of each join between the tables.

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

    Re: sorting a memo field? (a2002)

    Another caution - sorting a memo field may truncate the memo field to 256 characters in some circumstances, so check carefully to make sure that isn't happening when you update the record. As an alternative we have used the Left(255) characters as a calculated expression and sorted on that.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting a memo field? (a2002)

    (Edited by HansV to reduce large screenshot in size)

    Hans,

    If I'm reading you right, the attached query should be updateable because the join fields on each table on the "one" side is Indexed Yes(no duplicates). But, it's not letting me add a value to the KeyWords field in tblRspndntsRspns.

    What am I missing?

    Elizabeth
    Attached Images Attached Images

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sorting a memo field? (a2002)

    Hi Elizabeth,

    In this case, the query is locked because there are too many joins - there are two "paths" from tblSurveys to tblRspndntsRspns. Remove one of the joins from tblSurveys; you don't need both.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting a memo field? (a2002)

    Hans,

    That took care of it and allowed me to clean up several other things as well. Sure glad you're around!

    Elizabeth

Posting Permissions

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