none
Memo Fields RRS feed

  • Question

  • Hi,

    I have code (VBA for MS Access) that checks it fields (text, boolean, number) are empty or not to structure custom made reports.

    I have not yet been able to right code (If *memo field empty" Then) that gives the desired result. Does anybody know a way to evaluate if the memo field contains text or not.

    Thanks for any suggestion on how to solve this problem that kept me from making progress lately. Bo Zett

    Tuesday, January 22, 2019 3:34 PM

Answers

  • Hi All,

    thank you all for your input. Code to evaluate the fact if a text field has content or not is quite straight forward. But when I decided to ask above question I had in mind a remark Alison Balter made in her invaluable book on Access that "Memo Fields cannot be sorted". This made me suspicious that the answer is more complicated than we like it. While I was waiting for your answers I was digging through the innumerable methods and properties of VBA object. I have the impression that memField.GetChunck() method has to be used to assess all or part of the memfields content.

    It is whether easy nor straight forward but I'm working at it. Bo Zett

    P.S. in the meantime I settled on the following code:

        Set rs = rs.OpenRecordset

        strChunk = rs!memInfo.GetChunk(0, 25)

        If Len(strChunk) > 0 Then

            Debug.Print "Info"

        Else

            Debug.Print "no Info"

        End If

    • Edited by Bo Zett Wednesday, January 23, 2019 11:51 AM further testing
    • Marked as answer by Bo Zett Wednesday, January 23, 2019 11:51 AM
    Wednesday, January 23, 2019 10:18 AM

All replies

  • Hi Bo,

    A memo field is just a text field. Have you tried any of the following?

    If IsNull([MemoField]) Then

    If [MemoField] & "" = "" Then

    If Len(Nz([MemoField],""))=0 Then

    Tuesday, January 22, 2019 3:42 PM
  • Does anybody know a way to evaluate if the memo field contains text or not.

    Hi Bo,

    You can use:     [memo field] > ""

    This expression yields True if the memo field IS NOT NULL and is not a zero-length string.

    Imb

    Tuesday, January 22, 2019 7:53 PM
  • Does anybody know a way to evaluate if the memo field contains text or not.

    Hi Bo,

    You can use:     [memo field] > ""

    This expression yields True if the memo field IS NOT NULL and is not a zero-length string.

    Imb

    Now that's an interesting and compact null/nullstring test I haven't seen before.  Thanks!
    Tuesday, January 22, 2019 11:06 PM
  • Now that's an interesting and compact null/nullstring test I haven't seen before.  Thanks!

    Hi Bruce,

    Nevertheless, I have "published" this already a couple of times.

    The inverse:   NOT ([memo field] > "")   does not work because of the typical behaviour of Null, but you can use:

        NOT ([memo field] & "" > "") , or simpler   ([memo field] & "" = "")

    These expressions I use all through my applications. I hardly work with Null-values for variables that will contain text. Though there is a theoretical difference between a Null-value and a zero-length-string, I have not yet found a practical reason to make distinction between them.

    Imb.

    Wednesday, January 23, 2019 8:22 AM
  • Hi All,

    thank you all for your input. Code to evaluate the fact if a text field has content or not is quite straight forward. But when I decided to ask above question I had in mind a remark Alison Balter made in her invaluable book on Access that "Memo Fields cannot be sorted". This made me suspicious that the answer is more complicated than we like it. While I was waiting for your answers I was digging through the innumerable methods and properties of VBA object. I have the impression that memField.GetChunck() method has to be used to assess all or part of the memfields content.

    It is whether easy nor straight forward but I'm working at it. Bo Zett

    P.S. in the meantime I settled on the following code:

        Set rs = rs.OpenRecordset

        strChunk = rs!memInfo.GetChunk(0, 25)

        If Len(strChunk) > 0 Then

            Debug.Print "Info"

        Else

            Debug.Print "no Info"

        End If

    • Edited by Bo Zett Wednesday, January 23, 2019 11:51 AM further testing
    • Marked as answer by Bo Zett Wednesday, January 23, 2019 11:51 AM
    Wednesday, January 23, 2019 10:18 AM
  • I had in mind a remark Alison Balter made in her invaluable book on Access that "Memo Fields cannot be sorted".

    Hi Bo,

    With my very old dear Access2003 I can sort any Memo field just by taking the left 255 characters:  Left([memo field],255)

    Imb.


    edit: just in case the memo field IS NULL, use:    Left([memo field] & "",255)
    • Edited by Imb-hb Wednesday, January 23, 2019 1:34 PM
    Wednesday, January 23, 2019 1:32 PM
  • Hi,

    I started way back with the 97 version. And Alison Barter's book is for me still the biggest help - even so the 2007 version  I currently use obviously has some features I tend to overlook J

    Wednesday, January 23, 2019 2:08 PM