To work around this problem, use one of the following methods.
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%"));
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, depending on the version of Access.
Access 2003
- Open the Northwind sample database.
- On the Insert menu, click
Query. - In the New Query dialog box, select
Design View, and then click OK. - On the View menu, click SQL View.
- In the Query window, paste the following query:
SELECT Customers.CustomerID
FROM Customers;
- On the File menu, click
Save. - In the Query Name box of the
Save As dialog box, type
Query1, and then click OK. - On the View menu, click
Datasheet View. - On the Records menu, point to
Filter, and then click Advanced Filter/Sort. - In the Field list, select
CustomerID. - In the Criteria box under
CustomerID, paste the following code: - On the Filter menu, click
Apply Filter/Sort. - On the File menu, click
Save. - On the File menu, click
Close. - In the Database window, click
Queries. - Right-click Query1, and then click
Export. - In the Export Query 'Query1' To dialog box, select XML in the Save as type list, and then click Export.
- In the Export XML dialog box, click
More Options. - 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.
Access 2007
- Open the Northwind sample database.
- On the Create tab, click Query Design, and then click Close.
- On the Design tab, click SQL View in the Results group.
- In the Query window, paste the following query:
SELECT Customers.[First Name]
FROM Customers;
- Click the Microsoft Office Button, and then click Save.
- In the Query Name box of the
Save As dialog box, type
Query1, and then click OK. - On the Home tab, in the Views group, click View, and then click Datasheet View.
- On the Home tab, click Advanced in the Sort & Filter group, and then click Advanced Filter/Sort.
- In the Field list, select First Name
- In the Criteria box under
First Name, paste the following code: - On the Home tab, click Advanced in the Sort & Filter group, and then click Apply Filter/Sort.
- Click the Microsoft Office Button, and then click Save.
- Right-click Query1, point to Export, click XML File, and then click OK.
- In the Export XML dialog box, click
More Options. - In the Export XML dialog box, click the Data tab.
- Under Records To Export, select the Apply existing filter option, and then click
OK. - In the Export - XML File dialog box, click Close.