Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Need Help Pulling Text Out Of Memo Field In Query (97)

    This should be easy but I've been struggling with it for hours and nothing works. Right now, I'm trying to pull out the left 20 characters of a memo field. The SQL I'm using is:

    Left([Problems]![Problem],20) AS Title

    It's returning #Error. I thought it might need Left$ but that does not help. Some of the entries might be less than 20 (minimum is four) so I changed the 20 to 2 and that did not work either. Finally, I tried replacing the Problem memo field with a string field and it still did not work. It's got to be something simple that I'm overlooking but I just do not see it.

    Any suggestions?

    If I ever get this working, I've got to deal with a) fields with less than 20 characters and [img]/forums/images/smilies/cool.gif[/img] getting this break to happen at a space between words if you have any suggestions about that. (Some fields have only one word with no spaces.)

    Ronny
    Ronny Richardson

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help Pulling Text Out Of Memo Field In Query (97)

    Maybe you have a missing reference problem.
    Open any module or create a new one.
    Select the menu Tools, References and look in the References window if you find a line with MISSING near the checkbox.
    If so uncheck it and close the reference window, close the module (no need to save) and retry your query.
    Francois

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

    Re: Need Help Pulling Text Out Of Memo Field In Query (97)

    It appears that you have a syntax problem:

    Left([Problems]![Problem],20) AS Title

    in a SQL string should probably read

    ... Left(Problems.Problem,20) AS Title .....

    where Problems is the table name you are SELECTing from and Problem is the field name. To see what I mean try building your query in the design view instead of the SQL view, and then view the sql the design view creates. To find the first space character you will need to use the InStr() function - check it out in help. It basically returns the character position of the first occurance of the character you specify, or 0 if it doesn't find one. You can then use that info in combination with the Left() function to get what you are after. Hope this helps solve your problem.
    Wendell

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help Pulling Text Out Of Memo Field In Query (97)

    Wendel,
    The point or the exclamation, you can use the two.
    But when you build the query with the query design, the square brackets are added automaticly.
    Francois

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Need Help Pulling Text Out Of Memo Field In Qu

    <P ID="edit" class=small>Edited by MarkD on 31-May-02 09:08.</P>There's no reason the Left function shouldn't work using syntax you described. If you use Left$ string function rather than Left in query expression then you will get #Error in query results for any record where the memo field is null. As recommended by Francois need to check References list in VB Editor for broken references. Assuming you can get your string functions to work properly, you can use a user-defined function similar to this in a query expression to get first 20 characters of memo field (or other specified length) without truncating word in middle when you go over specified length:
    <pre>Public Function TruncateMemo(varMemo, intLen As Integer) As String
    On Error Resume Next
    'varMemo is text to be truncated
    'intLen is length to truncate text to (number of characters)
    Dim n As Integer

    If Len(varMemo) = 0 Or IsNull(varMemo) Then
    TruncateMemo = ""
    ElseIf Len(varMemo) <= intLen Then
    TruncateMemo = Trim(varMemo) 'May be trailing spaces
    Else
    intLen = intLen + 1 'May be space after last character
    varMemo = Left(varMemo, intLen)
    If InStr(1, varMemo, " ") = 0 Then 'No spaces found
    TruncateMemo = ""
    Else 'Find last space in string
    For n = intLen To 1 Step -1
    If InStr(n, varMemo, " ") = n Then
    Exit For
    End If
    Next n
    TruncateMemo = Trim(Left(varMemo, n - 1))
    End If
    End If

    End Function</pre>


    Example of query expression:
    <pre>SHORT COMMENT: TruncateMemo([COMMENTS], 20)</pre>

    where COMMENTS is memo field, 20 is specified length; qualify with table name if necessary.
    Either syntax: TABLENAME.COMMENTS or [TABLENAME]![COMMENTS] - should work in query expression.
    (Edited to fix bug in function originally posted.)
    HTH

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help Pulling Text Out Of Memo Field In Query (97)

    I started writing this in query builder mode and it was Access that structured it as:

    Left([Problems]![Problem],20) AS Title

    When I could not get it to work in query builder, I switched to SQL view to see if I could spot any mistakes. Changing the expression to

    Left(Problems.Problem,20)

    had no impact, I still get #Error.

    Ronny
    Ronny Richardson

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help Pulling Text Out Of Memo Field In Query (97)

    There were a ton that were not checked but none said missing.

    Ronny
    Ronny Richardson

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help Pulling Text Out Of Memo Field In Qu

    I have not got the Left to work yet but if I do, does this code then become part of the query itself (e.g. in SQL view) or does it go in the Macro tab?

    Ronny
    Ronny Richardson

  9. #9
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help Pulling Text Out Of Memo Field In Qu

    Put ?Left("ABCDEF",3) into the debug window and see what results you get

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help Pulling Text Out Of Memo Field In Query (97)

    Can you attach the db or if it is to big, send it to my e-mail (see my profile). I'll test it on my Access 97
    Francois

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help Pulling Text Out Of Memo Field In Qu

    This is really weird. First, I don't know how to use the debug window so I just put the expression Left("ABCDEF",3) into a query. (I also tried Left$) I get the message:

    "The expression is typed incorrectly, or it is too complex to be evaluated..."

    Switching to another large database that I have, I tried the following expression:

    Left(Sales.Describe,10) AS Test

    where Describe is also a memo field. It works just fine! So, the problem has something to do with this one specific database and not the syntax itself since the exact same syntax works in another database.

    I've tried to figure out how to post a zipped copy of the database here in the forum but I cannot figure it out. It may be my old browser (Netscape 4) but I know I've posted files before. I guess I can email a zipped file to anyone who is interested.

    Ronny
    Ronny Richardson

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Need Help Pulling Text Out Of Memo Field In Qu

    To be able to use TruncateMemo function in a query expression, you would need to copy and paste the code into any standard code module. To add a new module, from Tools menu select Macro, then Visual Basic Editor, or simply enter ALT + F11. Once VBE is open, select Module from the Insert menu. A new blank code module will open. Copy entire function to new module and save. You will then be able to use this function anywhere in your project, including queries. If using the Query Design Expression Builder, expand the "Functions" folder and a folder with same name as your project (usually the database name) will appear under the "Built-in Functions" folder. Use this second folder to locate any custom functions you have defined in a standard or class module.
    HTH

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help Pulling Text Out Of Memo Field In Query (97)

    We exchanged a couple of email messages and it turns out that it was the checkboxes causing the problem. I unchecked one box and everything is working great now. I appreciate everyone who helped out.

    Ronny
    Ronny Richardson

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help Pulling Text Out Of Memo Field In Qu

    I have the Left function working now so I am trying to get TruncateMemo to work.

    >To be able to use TruncateMemo function in a query expression,
    >you would need to copy and paste the code into any standard
    >code module.
    >
    >
    >To add a new module, from Tools menu select Macro, then Visual
    >Basic Editor,

    This does not work in Access 97. Selecting Tools and then Macro gives you four options:

    1. Run macro
    2. Create menu from macro
    3. Create toolbar from macro
    4. Create shortcut menu from macro

    >or simply enter ALT + F11.

    Also does not work in Office 97. I press Alt and F11 and nothing happens.

    I got around this by clicking on New on the Modules tab and then pasting the code.

    The TruncateMemo function does show up on the list of functions for this database (the only one) in the Build view but any attempts to use the function yields an undefined function error message.

    Any suggestions?

    Ronny
    Ronny Richardson

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help Pulling Text Out Of Memo Field In Qu

    Ronny,
    On the database window tab Modules, Click new to create a new module.
    Copy the code from the message from Mark and Past it in the new Module.
    You will have to format the text with enters at the right places because it will all be pasted on one line.
    When the code in the module window looks exactly like Mark's post, close the module and save it with any name but not TruncateMemo.
    A module may not have the same name as a function.
    Now you can replace Left(Promes,20) in the query with TruncateMemo(Promes,20).
    If this don't work, look in your mailbox, I've done it for you and send the database with the function and modify the query.
    Francois

Page 1 of 2 12 LastLast

Posting Permissions

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