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

Topic

New Reply

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 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

Reply | Quote

Replies

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.

Reply | Quote

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

Ronny

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Edited by MarkD on 31-May-02 09:08.

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:

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

Example of query expression:

SHORT COMMENT: TruncateMemo([COMMENTS], 20)

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

I made the mistake of naming the module TruncateMemo. With that changed, everything works great. Thanks for your help and the help of everyone else in the forum.

Ronny

Reply | Quote

Sorry for misleading instructions – I haven’t used Access 97 in ages and forgot that Access 97 doesn’t use the standard VBE. Here (at work) all we have is Access 2K. Glad you got problem fixed.

Reply | Quote