Memo Fields

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

Answer 1

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

Answer 2

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

Answer 3

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!

Answer 4

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.

Answer 5

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

Answer 6

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)

Answer 7

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