Background
In some cases conditions are case-sensitive. This is dependent the specific Office Connector feature you are using to query data and how it is being used.
The following list identifies when case-sensitive comparisons would occur:
In all other cases, the conditions will not be case-sensitive. Refer to the related questions listed below for information about forcing comparisons that are not case-sensitive in the above scenarios.
Technical Explanation
In the case of a Query Table, the conditions are always processed at the database server (as described below).
For performance reasons, Office Connector uses one of two methods to process the filter argument of an Office Connector Worksheet Function. The specific method applied depends on the function and the table-name argument.
Method 1 – Data is Filtered at the Database Server
Because some tables can contain a large volume of data, Office Connector delegates the processing of aggregate functions (either TSCount or TSSum) to the database server. This improves processing time by allowing the server to process the request and also by reducing the amount of data that needs to flow between the server and your computer.
Office Connector automatically uses this method with the TSCount and TSSum functions when the table-name argument contains the name of a table (not an SQL statement). Note that functions created using an Office Connector wizard will always specify a table name. The use of an SQL statement is an advanced technique sometimes used to optimize performance with large databases.
When the conditions are processed by the database server, the Sage 300 Construction and Real Estate ODBC driver is responsible for carrying out the comparisons. When the using the LIKE operator or a List Item Field, the ODBC driver performs the comparison in a case-sensitive manner.
Method 2 – Data is Filtered Locally by Your Computer
When processing a TSFind function or an aggregate function (either TSCount or TSSum) that utilizes an SQL statement in the table-name argument, Office Connector executes the query and holds all of the results from the query in memory on your computer (like a Query Table that is completely hidden from view). The filter is then applied to the data in memory using Microsoft Data Access components that reside on your computer. The Microsoft Data Access components work differently than the Sage 300 Construction and Real Estate ODBC driver in regard to how conditions are applied. This results in comparisons that are not case-sensitive.