CODEWITHSUNDEEP
sql-serverms-access
bernie2436
bernie2436

Reputation: 23901

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

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?

Upvotes: 7

Views: 40201

Answers (2)

Oleksii Kyslytsyn
Oleksii Kyslytsyn

Reputation: 2426

select LENGTH(nTextFieldName) from table_name;

Upvotes: -2

JonH
JonH

Reputation: 33153

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)))

Upvotes: 14

Related Questions