How to compare fields with 'Memo' datatype in vba?

Question

The MS access database does not allow comparing fields with 'memo' datatypes in SQL query. Is there a way to do it in VBA?

Simple comparison does not work if the size of memo>255 characters

Answer 1

When using a recordset, you can compare them.

The table : Table1(int, memo, memo)

id   memo1     memo2
1    A      B
2    D      C

The script :

Public Sub test()

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSql As String

Set conn = CurrentProject.Connection
strSql = "select * from Table1"

rst.Open strSql, conn

rst.MoveFirst
    While Not rst.EOF
        Debug.Print rst("memo1") > rst("memo2")
    rst.MoveNext
Wend

rst.Close
conn.Close

End Sub

And the output :

False
True

Is that what you mean?

UPDATE : it works fine for me with fields with length of 4000 chars up, just tested it, also the length function works fine (vba from access 2003)

Greetings, peter

Answer 2

You probably have something specific in mind but this simple case works OK with no error:

    CREATE TABLE TestMemo (memo1 MEMO, memo2 MEMO)
    ;
    INSERT INTO TestMemo (memo1, memo2) VALUES ('Blah', 'Blah')
    ;

INSERT INTO TestMemo (memo1, memo2) VALUES ('123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A', '123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789Z')
;

    SELECT memo1, memo2 
      FROM TestMemo
     WHERE memo1 = memo2
    ;

I am aware that this truncates at 255 characters. Is this your problem? - Edit: I've tested it with values exceeding 255 characters and it isn't being truncated.

Answer 3

I guess the best way is save two Memo fields into 2 files and then do the comparison with files.