Access queries with a wildcard character do not export data to an XML document (823224)

WORKAROUND

• Method 1
  
  You can rewrite the query to replace the Jet-specific wildcard character with the ANSI 92 wildcard character. For example, the original query may look similar to this with the * wildcard character:

  SELECT Customers.CustomerID
  FROM Customers
  WHERE (((Customers.CustomerID)  Like "A*"));

  You can rewrite the query as follows:

  SELECT Customers.CustomerID
  FROM Customers
  WHERE (((Customers.CustomerID)  Like "A%"));

  Note If the Access database is not configured for ANSI 92 compatibility, when you run the rewritten query from Access, no data is returned. However, the exported XML document that is based on the rewritten query returns the expected results.
• Method 2
  
  You can rewrite the query so that the criterion of the query does not contain wildcard characters. For example, the original query may look similar to this with the * wildcard character:

  SELECT Customers.CustomerID
  FROM Customers
  WHERE (((Customers.CustomerID)  Like "A*"));

  You can rewrite the query as follows:

  SELECT Customers.CustomerID
  FROM Customers
  WHERE Left(CustomerID,1) = "A";

• Method 3
  
  When you export data to an XML document, you can specify whether to apply an existing filter on the object. Instead of specifying a wildcard character in the criteria of the query, you can create a filter with the filter string equivalent to the criteria that you want (such as "A*") and then apply this filter during the Export XML process.
  
  To do this, follow these steps:
  1. Open the Northwind sample database.
  2. On the Insert menu, click Query.
  3. In the New Query dialog box, select Design View and then click OK.
  4. On the View menu, click SQL View.
  5. In the Query window, paste the following query:

     SELECT Customers.CustomerID
     FROM Customers;
     

     Note You can see that the criterion of the query is removed. Therefore, the query does not contain any wildcard characters.
  6. On the File menu, click Save.
  7. In the Query Name box of the Save As dialog box, type Query1.
  8. On the View menu, click Datasheet View.
  9. On the Records menu, point to Filter and then click Advanced Filter/Sort....
  10. In the Field list, select CustomerID.
  11. In the Criteria box under CustomerID, paste the following code:

      Like "A*"

  12. On the Filter menu, click Apply Filter/Sort....
  13. On the File menu, click Save.
  14. On the File menu, click Close.
  15. In the Database window, click Queries.
  16. Right-click Query1 and then click Export.
  17. In the Export Query 'Query1' To... dialog box, select XML in the Save as type list and then click Export.
  18. In the Export XML dialog box, click More Options.
  19. In the Export XML dialog box, select the Data tab. Under Records To Export, click to select the Apply existing filter check box and then click OK.