>>>>>>I ran the code (big thanks you for the suggestions to Tore and Antonio) that returns ODBC drivers I have installed on my computer. And here are the 3 drives I see:
>>>>>>SQL Native Client / Version 09.00
>>>>>>SQL Server Native Client 10.0 / Version 10.00
>>>>>>SQL Server Native Client 11.0 / Version 03.80
>>>>>>
>>>>>>The word "native" confuses me since I thought that the SQL Server Native Client does not work with Varchar(MAX). But I do use Varchar(MAX) on this computer and it works. Does it mean that the first one listed above, SQL Native Client / Version 09.00 is the "old" ODBC driver that does work with Varchar(max)?
>>>>>
>>>>>This post may be helpful:
http://blogs.msdn.com/b/sqlnativeclient/archive/2008/02/27/microsoft-sql-server-native-client-and-microsoft-sql-server-2008-native-client.aspx.t>>>>
>>>>Thank you very much, Antonio.
>>>
>>>You're welcome.
>>>
>>>I am struggling with these beasts, too, mostly regarding date related data types, so, it's a common investigation we're embracing...
>>
>>I don't remember all the details, but it's really a pain as you said. I know that the latest driver doesn't work with varchar(max) and SQL pass through, you actually end up with blank memo fields. That's why I always use Text type fields instead, although they are "not recommended for new design" and may be unsupported in newer version of MSSQL. And with the older driver you will get access to varchar(max) fields, but not date (or was it datetime, I don't remember). Plus the access to varchar(max) fields is limited to about 8000 characters, if I remember correctly.
>
>One of the reasons I wanted to change Text type to varchar(max) is that LEN(field_name)>0 or LEN(field_name)=0 does not work on Text type. And all my program queries are based on this type of check if a field has entry or not. Varchar(max) works well with LEN().
I always use Empty(), and it works fine for Text fields also. I seriously doubt that Len() will fail, but I don't have the chance to check this right now since I am busy packing for a two weeks' vacation in the sun.
