DAM Extract from Remote Database Not Same As Local Extract

• Microsoft Excel for the Macintosh, versions 3.0 and 4.0

SUMMARY

When you use the Data Access Macro (DAM) to extract records from a remote (external) database you may get different results than you would if you performed the same extract from a database that is defined in a local Microsoft Excel file.

The reason for the different extract results is that, in most cases, Microsoft Excel assumes that the extract criteria contain only partial strings of text while the DAM assumes that the extract criteria contain complete strings of text. For example, when you extract records based on criteria where a city field value equals "San", a local Microsoft Excel database would return any city beginning with the word "San" such as San Francisco, San Jose, and San Diego. However, the Data Access Macro will only return the city "San."

Workaround

To work around this problem, choose the SQL Query or Query Assistant commands from the Data menu and manually enter the following query:

SELECT Table1.name, Table1.address, Table1.city, Table2.phone FROM Table1, Table2 WHERE Table1.city LIKE '%San_%' FOR EXTRACT; PRINTALL;

Note that this type of query is case sensitive, so a record containing a city field "santa rosa" would not be selected. To handle cases where the city is lowercase, use an OR operator in the WHERE clause, as in the following example:

SELECT Table1.name, Table1.address, Table1.city, Table2.phone FROM Table1, Table2 WHERE (Table1.city LIKE '%San_%') OR (Table1.city LIKE '%san_%') FOR EXTRACT; PRINTALL;

Microsoft has confirmed this to be a problem in Microsoft Excel for the Macintosh versions 3.0 and 4.0. We are researching this problem and will post new information here as it becomes available.

MORE INFORMATION

When the Data Access Macro extracts information based on defined criteria and extract ranges it translates the criteria to an SQL query before it passes the query to the Data Access Language (DAL) software.

The Data Access Macro uses the extract range to determine the SQL query SELECT clause and the criteria to determine the WHERE clause. For example, if A1:A2 is defined as the criteria, where A1="Table1.city" and A2="San", and A4:C4 is defined as the extract range, where A4="Table1.name", B4="Table1.address", and C4="Table1.city", the Data Access macro will translate the criteria into the following SQL query:

SELECT Table1.name, Table1.address, Table2.phone FROM Table1 WHERE Table1.city = "San" FOR EXTRACT; PRINTALL;

This particular query will return records containing only the text "San" in their city fields. The same query in Microsoft Excel would extract all records containing the text "San" as the first three letters in their city field.