null memo field

[LGarcia]

Hi all,
Can someone tell me the correct syntax for returning records where the memo
field is not null?
This returns all records: SELECT tblAudit.AuditID, tblAudit.AuditUser,
tblAudit.AuditTime, tblAudit.AuditTable, tblAudit.AuditBefore,
tblAudit.AuditAfter
FROM tblAudit
WHERE (((tblAudit.AuditAfter) Is Not Null));
AuditAfter is a memo field.
TIA,
LGarcia

 

[Douglas J. Steele]

If that's returning all rows, it implies that your blank memo fields aren't
Null.

To test whether that's the case, try

SELECT tblAudit.AuditID, tblAudit.AuditUser,
tblAudit.AuditTime, tblAudit.AuditTable, tblAudit.AuditBefore,
tblAudit.AuditAfter
FROM tblAudit
WHERE (((Len(tblAudit.AuditAfter & " ")) > 0));

That may be slow, though.

 

[LGarcia]

Thanks that did it! Didn't notice any slow down.

If that's returning all rows, it implies that your blank memo fields aren't
Null.

To test whether that's the case, try

SELECT tblAudit.AuditID, tblAudit.AuditUser,
tblAudit.AuditTime, tblAudit.AuditTable, tblAudit.AuditBefore,
tblAudit.AuditAfter
FROM tblAudit
WHERE (((Len(tblAudit.AuditAfter & " ")) > 0));

That may be slow, though.

 

[Douglas J. Steele]

I just noticed there's a mistake in what I suggested!

It should be

WHERE (((Len(tblAudit.AuditAfter & " ")) > 1));

or

WHERE (((Len(tblAudit.AuditAfter & "")) > 0));