How do I count the number of characters in SQL server ntext (i.e. memo) field in an access query?
ms-access, sql-server
Solution
`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)))`
Problem
I want to write an access query to count the characters in an ntext field in a linked SQL server table. In SQL server, I would just use this command (which wont work in access): ``` select datalength(nTextFieldName) //this command works on sql server but not in access ``` In access, I can only find the len command, which wont work on ntext fields: ``` select len(nTextFieldName) // access says nText is not a valid argument to this function. ``` Googling around, I've found a bunch of posts saying to use len, which is giving me an error. What is the command?