How do I count the number of characters in SQL server ntext (i.e. memo) field in an access query?

ntext type doesn't work with LEN. This specific type as well as a few others are deprecated:

ntext, text, and image data types will be removed in a future version of Microsoft SQL 
Server. Avoid using these data types in new development work, and plan to modify applications 
that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more 
information, see Using Large-Value Data Types.

The best way to handle this is to convert/cast the datatype to one that works such as varchar(max)/nvarchar(max) and only then get the LEN.

SELECT LEN(CAST(nTextFieldName As nvarchar(max)))