char_length function is not recognized

Question

Using Access 2010, I get the error

Undefined function 'char_length' in expression' 

when I try to run the query

Select * from SIMPLIFIEDSTREETSEGMENT where HWY_NUM is not NULL and CHAR_LENGTH(HWY_NUM)<2;

It works if I use LEN instead of CHAR_LENGTH.

Why?

Answer 1

Like most SQL dialects, ACCESS SQL is not 100% compatible with all other SQL dialects. The basics are the same, but there are differences. AS DBGuy stated, there is no CHAR_LENGTH function in Access. 

Selecting the ANSI 92 standard works for syntax, not for functions. So it may allow for using % as a wildcard for example, its not going to include Functions.

Answer 2

Thank you all for your replies. I am working in a GIS environment and want to move feature layers from ArcMap shapefiles to MS Access storage without having to update the SQL queries used to define subclasses for labelling purposes. Based on your replies, it seems I will have to substitute LEN for CHAR_LENGTH everywhere. Not a huge problem, but annoying. Thanks again.

Answer 3

Hi. If you want to use T-SQL in queries to, for example, execute stored procedures or functions, you could try using a Pass-Through query in Access. Just a thought...

Answer 4

Thanks for the reply. I am new to Microsoft Access. Can I use SQL 92 for Access queries? I tried selecting the SQL 92 syntax option in File>Options>Object Designers>Query Design, but now I get this error:

Reserved error (-1001); there is no message for this error.

Answer 5

Hi. CHAR_LENGTH() is not a built-in function in VBA, but LEN() is.